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:
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 ...