Hi, it does not appear the SCD component will do the job here. So this relates back to an earlier post on doing the updates, inserts programatically with execute t-sql tasks. I just illustrate the basic method to accomplish this with code using sets as your base and not row by row. This should be a lot faster. You will need to translate the example to fit your perticular columns/tables as appropriate. I represent a staging table and the destination table is where I move the rows from the staging to. Here new rows get inserted, existing rows get "deactivated" and a newer one inserted, and those not in the latest batch also get deactivated (meaning historical now, not current).

/* find those columns already existing,

and deactivate them to indicate they are not the latest version of the record (employee here),

It will look something like ... */

update d

SET d.validTo=getdate()

from staging as s inner join Destination as D

on s.colm1=d.colm1 [and ...=... ] /* <-- I have a feeling this will be a long list, indexing can help*/

/* Next insert the latest version of these existing records */

Insert into Destination

SELECT s.*, getdate() as validFrom, Null as validTo

from Staging as S inner join Destination as D

on s.colm1=d.colm1 [and ...=... ] /* same as above */

/*Next insert only the new records, these employees (or employee date combo, however you do it)

did not exist before ... */

Insert into Destination

SELECT s.* , getdate() a validFrom, null as validTo

from Staging as s LEFT JOIN destination as D on

s.colm1=d.colm1 [and ...=... ] /* same as above */

WHERE d.colm1 is null [and d.colm2 is null. ... ..] /*Same columns as in the join , and implies do not exist yet in the destination,thus new inserts*/

/*

Here are those records in the destination table already that are not in the staging set of records, these maybe are left alone.

Or if need be you can do as just above but make the destination table the LEFT table instead of the right one.

This would be if you have a situaion where a employee is no longer with the company so you may do something like this to deactivate them..*/

Update D

SET d.validTo = getdate()

FROM destination as D LEFT JOIN Staging as s

On s.colm1=d.colm1 [and ...=... ] /* same as above */

WHERE S.colm1 is null [and S.colm2 is null. ... ..] /* implies are not in the staging set but

exist in the destination from a prior import */

So this gives you the basics. Have a good look and see if you can wrap your head around it. It is fairly straight forward and what the MERGE operator does in the background essentially. I think this will be able to meet the need you have here, and breaks it down so that you see exactly what is happening (emulates a type 2 SCD).