SQL: HowTo import new rows to a Domain table from a flat file

Terminology:

  • 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

PreRequisites:

  • 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)
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: