19
Feb

Overview of the SQL Server MERGE command. Very useful for data warehouses

The purpose of Merge is where you have two tables – maybe a transaction table and a staging table which could have records to be inserted and/or replaced in the transaction table.  Or maybe you have two tables of people (staff or patients) – one table (the master) with last years data and one table from this year, and you want to bring the master up to date with additions, updates or removals.

Under SQL 2005 you would have had to create 3 different commands:

1)  DELETE FROM MASTER where not in the staging table (get rid of deleted records)
2)  INSERT INTO MASTER where in staging, but not in master (add the new)
3)  UPDATE MASTER SET… (update the changed)

With the new MERGE command, all updates, deletes and inserts could be processed via a more elegant and vastly more efficient command as follows:

– MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;

MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE ‘S%’
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE ‘S%’
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

Here are the results of the OUTPUT clause.

$action   EmployeeID  EmployeeName EmployeeID  EmployeeName
——— ———– ———— ———– ————
DELETE    NULL        NULL         101         Sara
DELETE    NULL        NULL         102         Stefano
INSERT    104         Steve        NULL        NULL

(3 row(s) affected)

1 Trackback or Pingback for this entry