Here are some notes on "SQL Server 2008 Merge" I took while attending an advanced class on SQL Server taught by Paul Nielsen (from http://www.sqlserverbible.com/).
Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.
- MERGE is like INSERT, UPDATE or DELETE all in one (UPSERT plus DELETE)
- MERGE INTO target USING source ON condition WHEN…
- 3 different types of when, use as many as you need:
- - WHEN MATCHED [AND (condition)] THEN … - typically UPDATE code
- - WHEN NOT MATCHED [BY TARGET] [AND (condition)] THEN … - typically INSERT code
- - WHEN NOT MATCHED BY SOURCE [AND (condition)] THEN … - typically DELETE code
- Requires a semicolon in the end so SQL can know when it ends...
- See http://msdn.microsoft.com/en-us/library/bb522522.aspx
Merge – Demo
- Combining flight check in list with final flight passenger list.
- In both lists – Checked in and Flew – WHEN MATCHED – Update seat
- Not in check in list – Walk in - WHEN NOT MATCHED [BY TARGET] – Insert
- Not in passenger list – No show – WHEN NOT MATCHED BY SOURCE – Delete
Merge – Alternatives?
- How did you do your “upsert” before? EXISTS? FULL OUTER JOIN?
- Many tricks used in the past, most focused on finding the “happy path”
- If you’re doing entire sets, the MERGE is usually better. It is a transaction in itself!
- When doing singleton-type updates, you might be able to find a faster way… Should you?
- See http://blogs.msdn.com/jenss/archive/2008/07/29/merge-it-your-own-way-followup-on-the-test.aspx