Snippet

SQL Server: UPSERT Without MERGE

Insert new rows and update existing rows safely without using MERGE.

Updated
Requires
SQL Server 2008+
Tags
sqlsql-serverupsertdata-fix
Markdown
View .md

Problem

You need to insert new records and update existing ones from a staging table. The MERGE statement can introduce unexpected behaviour and locking issues in some environments.

Solution

Split the operation into an UPDATE followed by an INSERT.

-- Update existing rows
UPDATE t
SET t.some_column = s.some_column
FROM target_table t
JOIN staging_table s ON s.id = t.id;

-- Insert new rows
INSERT INTO target_table (id, some_column)
SELECT s.id, s.some_column
FROM staging_table s
LEFT JOIN target_table t ON t.id = s.id
WHERE t.id IS NULL;
Load rows pasted from a spreadsheet into a #temp table to use as the staging table, in List to SQL Opens the tool with this example filled in

Notes

  • Easier to reason about than MERGE.
  • Avoids known MERGE edge cases in SQL Server.
  • Allows different validation logic for updates vs inserts.
  • Often performs better for large batches.