SQL Server 2008 Merge

  • Article

Here are some notes on "SQL Server 2008 Merge" I took while attending an advanced class on SQL Server taught by Paul Nielsen (from https://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

  • 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 https://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?