SSIS best way to compare target and staging database to insert and update

Question

SSIS best way to compare target and staging database to insert and update

sqlfriend

SSC Guru

Points: 52781
More actions
November 22, 2011 at 10:20 am

#250993

I have a ssis package with data flow tasks etc.
I would like to do incremental import and update instead of using truncate or delete to import data from staging database to target database.
What is the best way to do this in the aspect of easy to maintenance later?
Basically I want to do is compare tables, if new records, insert, if no longer exist, delete, if exist but some columns are different, then update.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

sqlfriends (11/22/2011)
I have a ssis package with data flow tasks etc.
I would like to do incremental import and update instead of using truncate or delete to import data from staging database to target database.
What is the best way to do this in the aspect of easy to maintenance later?
Basically I want to do is compare tables, if new records, insert, if no longer exist, delete, if exist but some columns are different, then update.
Thanks

For easy maintenance, use stored procedures. The merge-statement in T-SQL will give you the ability to compare and insert/update rows.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

compare tables, if new records, insert, if no longer exist, delete, if exist but some columns are different, then update.

So you want to compare every column in every row? I would advise trying the staging table method and comparing performance before you decide which method to use.

The merge-statement in T-SQL will give you the ability to compare and insert/update rows.

Although this is a SQL Server 2005 forum, so MERGE may not be an option.

John

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

John Mitchell-245523 (11/23/2011)
The merge-statement in T-SQL will give you the ability to compare and insert/update rows.
Although this is a SQL Server 2005 forum, so MERGE may not be an option.
John

My apologies...missed that.

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Thanks, actually I'm using SQL 2008, for I did't see there is a forum about ssis in 2008 , other than this one and the one about data warehouse.

For merge in SQL, it is new to me, is it as flexible as using Select ...left join on.... on column is null...

pattern to compare tables to insert and update.

Thanks

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 5

For those using 2005, I would recommend doing a left join from the staging table to the target table, including in the SELECT statement of the columns that would be a NULL. Use that column in a conditional split to route the new rows (value is null) to an insert; all others go to an update.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

sqlfriends (11/23/2011)
For merge in SQL, it is new to me, is it as flexible as using Select ...left join on.... on column is null...pattern to compare tables to insert and update.

It is even more flexible 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

RonKyle (11/24/2011)
For those using 2005, I would recommend doing a left join from the staging table to the target table, including in the SELECT statement of the columns that would be a NULL. Use that column in a conditional split to route the new rows (value is null) to an insert; all others go to an update.

Expanding on this:

the INSERTS can go directly to the table using the fast load option of the OLE DB Destination.

For UPDATES, I recommend to write them to a staging table and then script out an UPDATE statement with an Execute SQL Task. This will be much faster for large datasets than using the OLE DB Command.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

sqlfriend

SSC Guru

Points: 52781

November 28, 2011 at 9:18 am

#1413498

Thanks all.

I will try all the options.

Sam Vanga SSCommitted Points: 1742 More actions · Answer 9

Following is an article that might help you. It's using SSIS 2005, but clearly explains all the concepts.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62063/

Sam Vanga
http://SamuelVanga.com