Microsoft SQL Server 2012 Bible

Merging Data

An upsert operation is a logical combination of an insert and an update. If the data isn't already in the table, the upsert inserts the data; if the data is already in the table, then the upsert updates with the differences. Ignoring for a moment the MERGE command in SQL Server, you can code an upsert operation with T-SQL in a few ways:

• The most common method is to attempt to locate the data with an IF EXISTS; if the row is found, UPDATE; otherwise INSERT.
• If the most common use case is that the row exists and the UPDATE is needed, then the best method is to do the update; if @@RowCount = 0, then the row was new, and the insert should be performed.
• If the overwhelming use case is that the row would be new to the database, then TRY to INSERT the new row; if a unique index blocked the INSERT and fired an error, then CATCH the error and UPDATE instead.

All three methods are potentially obsolete with the new MERGE command. The MERGE command is well done by Microsoft — it solves a complex problem with a clean syntax and good performance.

First, it's called “merge” because it does more than an upsert. Upsert inserts or updates only; merge can be directed to insert, update, and delete all in one command.

In a nutshell, MERGE sets up a join between the source table and the target table and can then perform operations based on matches between the two tables.

To walk through a merge scenario, the following example sets up an airline flight check-in scenario. The main work ...