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