October 1, 2007 at 12:46 am
I have a table with a primary key. Now I need to import a flatfile with all the new and changes rows since the last day.
The SISS package should insert if the primary key not yet exists in the destination table. If the primary key exists already, it should update the row.
How can I do that?
thanks
October 1, 2007 at 5:24 am
Drag a Slowly Changing Dimension wizard into your data flow.
I am not going to explain it in detail here, but you specify a type of SCD (in your case it will be a type 1), key columns (in your case, your PK field), and which columns you wish to update if they have changed.
The component itself will determine if a record exists or not and if it has changed and then directs the output to in insert or update if necessary.
There are two things to note.
It can be slow - large tables will take some time to process.
It is very data type, trailing space, and case sensitive. You need to use derived columns and type conversions to make sure everything matches the type, length, etc. of the final table before you use the wizard. Some of this can be configured and some things will not cause an update, but it is safest to make sure all type casts and trimming is done up front.
October 2, 2007 at 11:54 am
The easiest thing would be to import this flat file to a holding/working/temp table and then run a simple SQL statement against it. The way you have worded the question, it looks like the flat file also has the PK in it. If so, once you have imported the flatfile into a temporary tblTemp, you could easily write a statement like this to do your job.
IF EXISTS (SELECT PKey from tblMain WHERE PKey in (SELECT DISTINCT PK from tblTemp))
UPDATE tblMain Set Col1 = tblTemp.Col1, Col2 = tblTemp.Col2, etc FROM tblMain WHERE tblMain.PKey = tblTemp.PK
ELSE
INSERT INTO tblMain (Col1, col2, col3...) VALUES (tblTemp.Col1, tblTemp.Col2, tblTemp.Col3....)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply