MERGE-SQL Server 2008

In SQL Server 2008 ETL operations one of the common situations that is handled is the requirement for capturing change record information or what is more colloquially called  as Deltas. One of the common requirement is say on first day of a production ETL process records come in into a SQL Server Database they are inserted. Then from second day onwards the process needs to look for new records that have come in and also records which have changed from day one. In case of records that have changed from day one, we need to update the existing record and add the new changed record into the table. This type of scenarios can be handled by the MERGE statement available in 2008. For example there are two tables say:
Contact and Contact_Master. The Contact table gets truncated everyday and records are populated, the contact_master table has all the records which includes new and updated records. The users access the Contact_Master table through views in order to get the information based on a date range. The following command would help us insert new and modified records into the master table.

INSERT INTO contact_master (contactid,contactname,activebitflag,createddate)

SELECT contactid,contactname,activebitflag,createddate FROM
( MERGE contact_master S
USING contact L
ON S.contactid=L.contactid
WHEN Matched
AND S.activebitflag=1
THEN UPDATE SET S.activebitflag=0
WHEN NOT MATCHED BY TARGET
THEN INSERT (contactid,contactname,activebitflag,createddate)
VALUES (L.contactid,L.contactname,1,GETDATE())
OUTPUT $action,1 AS activebitflag,L.contactid,L.contactname,GETDATE() as createddate
) AS ActiveInserts (Action,activebitflag,contactid,contactname,createddate)
WHERE Action='UPDATE';

There are different parts to the SQL statement: The Merge statement has a MATCHED section which handles updates of records matched between contact and contact master. The NOT MATCHED section handles the insert of new records.There is OUTPUT clause in MERGE Statement that can be useful, there is a variable called $action which holds the keywords such as INSERT,UPDATE and DELETE, this can be very useful especially when one wants to know how many records got inserted.updated and deleted as part of the Merge Statement. In the above statement, the results of the merge are captured in a derived table. In the derived table all the records which were flagged as UPDATE as part of initial merge, get inserted as new records into the table, so a record which already exists gets updated and also gets inserted with a different flag. This is one of the strategies used to capture delta information.