- Domain table: A table with a function like an Enum in C#. In some cases it needs new lines, but never needs changes.
- ImportTableTemp: a table that is a mirror of a flat file with only varchar as column types
- Delete all rows from ImportTableTemp
- Import rows to ImportTableTemp from a flat file
Now use the following join to add new rows from the flat file to the domain table.
Note: In this example the Domain table uses a varchar as ID.
Insert Into TargetDomainTable (ID, Name) -- targettable Select Distinct IT.TTID, IT.TTName -- data from importtable From ImportTableTemp IT -- importtable Left Outer Join TargetDomainTable TT On IT.TTID = TT.ID -- all rows from importtable Where TT.ID is NULL -- targettable has not data AND IT.TTID is not NULL -- importtable has data AND IT.TTID != '' -- importtable has data (optional - when TTID is a varchar)