March 14, 2005 at 2:11 pm
I have used dts to import records and usually truncate the target table first; thus all records are inserts. I have a table with constraints so I can not truncate the target table. What is the best way to code dts so it insert new records and update existing records? Thanks for the help!!
March 14, 2005 at 2:56 pm
It seems to me that you should look into the Data Driven Query task. It lets you perform inserts or updates based on the data in the source. I wish I knew about it when I was developing some stuff a few years ago.
Another technique I used was to dump the data into a temporary table and then use a series of Execute SQL Tasks to perform inserts, updates, or deletes based upon the existence of the records in the temp and target tables. If the target table had subtables (and so I had to keep referential integrity), I would simply delete the child records for each updated record in the target table and simply re-insert all the new child records from the data source. That way, I wouldn't have to try and figure out which child record matched which one and whether or not they were updates or inserts. Makes things a lot easier.
Also, depending upon your constraints, you could just do a DELETE FROM statement instead of a TRUNCATE. I use this if I'm filling lookup tables before the main data tables have received their data.
Hope this helps!
March 14, 2005 at 2:57 pm
I would stage the new data into a 'termporary' table that you truncate before each load. Once loaded into that table, then you just run set-based SQL operations between this new copy of the data and the existing target table:
INSERT INTO .... WHERE NOT EXISTS
and
UPDATE SET .... WHERE OLDKEY = NEWKEY AND (OldCol1 <> NewCol1 Or OldCol2 <> NewCol2 ... etc)
March 14, 2005 at 3:26 pm
Exactly. However, it may be worth testing to see if the column comparisons in the WHERE clause on the UPDATE is worth doing. That can add a lot of overhead. It may be more efficient to simply update all the existing records instead of updating only those which have changed. I'd be curious to see which is more efficient. Of course, there could be other factors such as triggers and indexes which may make the writes slower...
March 14, 2005 at 3:50 pm
What I often end up doing is:
Update PermanentTable
Set Col1 = s.Col1
...
ColN = s.ColN
From PermanentTable As p
Inner Join StagingTable As s
ON (p.KeyCol = stage.KeyCol)
Where Checksum(p.Col1, p.Col2 ... p.ColN) <>
Checksum(s.Col1, s.Col2 ... s.ColN)
May 18, 2006 at 8:54 am
Does anyone have an example of DTS using the Data Driven Query task to perform insert else update task?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply