lihao:

I do this sort of thing all the time, primarily for bulk-loading data. The method I usually use is to first load the data into a holding table (no keys, so loading is fast & easy):

create table #HOLD (
   ...
   property      varchar(50),
   ...
   record_state  char(1)
)
[download]

I next check constrained columns and do some combination of:

• (a) report new values found in the table,
• (b) mark the records in the holding table that contain unknown values,
• (c) generate new entries in the dictionary table, or
• (d) delete the records containing the unknown values.

-- (a) Report new values
select distinct property 
from #HOLD
where property not in (select property from table2)

-- (b) Mark records containing those new values
update #HOLD set record_state='N'
where property not in (select property from table2)

-- (c) Generate new dictionary items (assumes auto-incrementing ID col
+umn)
insert table2 (property)
select distinct property
from #HOLD
where property not in (select property from table2)

-- (d) Delete records containing an unknown value (assumes you didn't
--     add new dictionary items)
delete #HOLD 
where property not in (select property from table2)
[download]

I can then update the main table(s) without worry:

-- Alter any records needing update
update table1 set
   ....
   property_id = T2.id
   ....
from table1 T1
join #HOLD H on H.PrimaryKeyColumn=T1.PrimaryKeyColumn
join table2 T2 on T2.property = H.property
where T1.property_id is null or T1.property_id != T2.id

-- Insert new records
insert table1 (..., property_id, ...)
select ..., T2.id as property_id, ...
from #HOLD H
where H.PrimaryKeyColumn not in (
   select PrimaryKeyColumn
   from table1
)
[download]

Finally, I can get rid of my holding table.

...roboticus

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.