May 30, 2011 at 1:31 am
I have a table in SQL say 'X'(i primary key int,j int, dt datetime) and it contains some data. The initial load of data is fine but I am confused about how to handle the incremental data. i.e. suppose initially X contains some data
1,2, 12-22-2010
3,4, 06-12-2011
and the next time the following data is coming into X
1,2,12-22-2010 3,4, 06-12-2011
5,6, 07-01-2011
3,7, 10-10-2011
so the X shall become,
1,2, 12-22-2010
3,7, 10-10-2011
5,6, 07-01-2011
can somebody please tell me how to handle the above situation using SSIS using different transformations and using pure sql?
May 31, 2011 at 10:24 am
If it were me, I would load the data in a staging table. Let's call that table Y. Then, I would use a MERGE statement (in a proc) like the following...
MERGE INTO X
USING Y
ON X.i = Y.i
WHEN MATCHED THEN
UPDATE SET
j = Y.j
,dt = Y.dt
WHEN NOT MATCHED THEN
INSERT(i,j,dt)
VALUES (Y.i, Y.j, Y.dt)
;
I'm not quite sure how to do this totally within SSIS though. I know it has a MERGE and MERGE JOIN data transformations, but I've never messed with them.
May 31, 2011 at 10:40 am
Thanks mate..thats an excellent solution using pure SQL..thanks again 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply