>Here's a little challenge. There are at least two different approaches that can be taken, depending on the version of SQL Server)
>
>Suppose I have 2 tables in SQL Server. One table is called TargetData - it is one row per person, with their hourly rate, and a flag for whether the person is active. We've got four rows.
>
>

>create table TargetData   (ID int, Name char(50), Rate Decimal(14,2), ActiveFlag bit)
>
>INSERT INTO TargetData VALUES 
>   (1, 'Kevin', 25.00, 1) ,        
>   (2, 'Steve', 40.00, 1), 
>   (3, 'Mike',   30.00, 1), 
>   (4, 'Jason',  35.00, 1)
>

>
>Now let's say that we receive an incoming "pipeline" of rows. We'll call it IncomingSourceData. For some rows we have a name or rate update...for some rows we have a new row (with respect to the TargetData table) based on ID....for some rows we have the same data (nothing changed)....and in one case we have a scenario where a row in the Targetdata isn't found in the Incoming Source data.
>
>

>create table IncomingSourceData (ID int, Name char(50), Rate Decimal(14,2))
>
>INSERT INTO IncomingSourceData VALUES 
>       (1, 'Kevin Goff', 25.00),     -- name change
>       (2, 'Steve', 41.00),            -- rate change
>      (3, 'Mike', 30.00) ,             -- no change
>     (5, 'John', 50.00);            -- new person
>

>
>Here's the goal, we want to take values in IncomingSourceData, and insert/update TargetData, based on the following rules:
>
>- the ID column is the key
>
>- If an ID from IncomingSourceData isn't found in TargetData, take the row in IncomingSourceData and insert it into TargetData
>
>- if an ID from IncomingSource data is found in TargetData, and either the name or rate has changed, issue an UPDATE to TargetData (but if those 2 non-key columns didn't change, don't do an update)
>
>- If an ID from TargetData isn't found in IncomingSourceData, mark the ActiveFlag in TargetData as 0 (inactive) for that ID

MERGE command in SQL Server 2008 and up makes this a no brainer.