Merge - Conditionally Insert, Update, or Delete

[Tuesday, February 15, 2011]

I came across the MERGE statement in SQL today. I love the InsertOrUpdate method from Hibernate SQL and this seemed like a nice way to get similar functionality in TSQL without writing all the if-then-else logic. Plus, you can grab the results of the merge statement and do what you want with it. Pretty clever. Here's a snippet from Technet.

-- 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's the full Technet Article on Inserting, Updating, and Deleting Data by Using MERGE.

Posted by: Benjamin Felt