Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 39 ยท Data Modification and Transactions

MERGE in SQL Server

Use SQL Server MERGE to insert, update and delete in one statement, see why many teams prefer update-then-insert patterns, and practice the idea with runnable stand-ins.

Often you receive a batch of data, a file of new prices or a feed of customers, and you must make a target table match it: insert the new rows, update the ones that changed and perhaps delete the ones that disappeared. Doing that with three statements means reading the data three times. T-SQL's MERGE does all three in one statement.

MERGE is powerful but has a reputation. It has had real bugs over the years, it needs care with concurrency, and many experienced developers write separate statements instead. This lesson shows the syntax, then the safer alternatives. For the portable idea, see also the SQL upsert lesson and the MERGE overview.

The shape of MERGE

You name a target table, a source (a table or query), and a join condition. Then you give actions for each situation:

SQL
MERGE dbo.Prices AS target
USING dbo.PriceFeed AS source
   ON target.Sku = source.Sku
WHEN MATCHED AND target.Price <> source.Price THEN
    UPDATE SET target.Price = source.Price, target.UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, Price, UpdatedAt) VALUES (source.Sku, source.Price, SYSUTCDATETIME())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.Sku, deleted.Price AS OldPrice, inserted.Price AS NewPrice;

The three branches:

Branch Meaning
WHEN MATCHED The row exists on both sides: usually UPDATE (or DELETE).
WHEN NOT MATCHED BY TARGET The source has a row the target lacks: INSERT. Also written just WHEN NOT MATCHED.
WHEN NOT MATCHED BY SOURCE The target has a row the source lacks: DELETE or UPDATE.

Rules to remember:

  • The statement must end with a semicolon.
  • Each target row may be matched by at most one source row. If two source rows match the same target row, SQL Server raises an error. De-duplicate the source first.
  • $action inside OUTPUT returns INSERT, UPDATE or DELETE for each row, a very useful audit tool.
  • The AND condition on WHEN MATCHED avoids pointless updates (and trigger noise) when nothing changed.

A runnable stand-in

SQLite has no MERGE. It does have an upsert, INSERT ... ON CONFLICT DO UPDATE, which covers the insert and update branches:

SQL Upsert from a feed (stand-in for MERGE)

Press Run (or Ctrl + Enter) to see the result here.

A-1 is untouched (same price), B-2 is updated, D-4 is inserted, and C-3 stays because this stand-in has no "not matched by source" branch. Add that branch with a separate DELETE:

SQL The NOT MATCHED BY SOURCE branch as a DELETE

Press Run (or Ctrl + Enter) to see the result here.

The classic alternative: UPDATE then INSERT

Many teams avoid MERGE and run two statements inside one transaction:

SQL
BEGIN TRANSACTION;

UPDATE t
SET    t.Price = s.Price
FROM   dbo.Prices AS t
JOIN   dbo.PriceFeed AS s ON s.Sku = t.Sku
WHERE  t.Price <> s.Price;

INSERT INTO dbo.Prices (Sku, Price)
SELECT s.Sku, s.Price
FROM   dbo.PriceFeed AS s
WHERE  NOT EXISTS (SELECT 1 FROM dbo.Prices AS t WHERE t.Sku = s.Sku);

COMMIT;

It is easier to read, easier to debug, and has no special syntax traps. For single-row upserts triggered by an application, the same pattern needs care with concurrency (see transactions).

Concurrency: the HOLDLOCK hint

MERGE is not atomic in the way people expect. Two sessions can both decide "not matched" and both insert, and one gets a duplicate-key error. The usual fix is WITH (HOLDLOCK) on the target:

SQL
MERGE dbo.Prices WITH (HOLDLOCK) AS target
USING (VALUES (@Sku, @Price)) AS source (Sku, Price)
   ON target.Sku = source.Sku
WHEN MATCHED THEN UPDATE SET Price = source.Price
WHEN NOT MATCHED THEN INSERT (Sku, Price) VALUES (source.Sku, source.Price);

HOLDLOCK makes the statement behave like the SERIALIZABLE isolation level for the target, so the key range stays locked until the statement ends. A UNIQUE or primary key on Sku is still your last line of defense.

When to use it

Use MERGE for set-based synchronization jobs, such as loading a dimension table in a data warehouse, when the source is clean and the job runs alone. For hot OLTP paths, prefer the explicit update-then-insert pair and test the concurrency behavior.

flowchart TD
  S["Source row"] --> J{"Match on key?"}
  J -- "yes" --> U["WHEN MATCHED: UPDATE or DELETE"]
  J -- "no, target lacks it" --> I["NOT MATCHED BY TARGET: INSERT"]
  T["Target row with no source"] --> D["NOT MATCHED BY SOURCE: DELETE"]

Every row of either table lands in exactly one branch.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which MERGE branch runs for a row that exists in the target but not in the source?

    WHEN NOT MATCHED BY SOURCE handles target-only rows, typically to delete or flag them.
  2. 2. What happens if two source rows match the same target row?

    MERGE refuses to update or delete the same target row twice. De-duplicate the source with ROW_NUMBER or DISTINCT first.
  3. 3. What does $action return inside OUTPUT?

    $action labels what MERGE did to each row, which is great for audit tables.
  4. 4. Why add WITH (HOLDLOCK) to a MERGE used as an upsert?

    HOLDLOCK keeps the key range locked for the duration of the statement, avoiding the race between check and insert.

Summary

  • MERGE combines insert, update and delete by comparing a source with a target.
  • It needs a semicolon, a one-to-one match and, for concurrent upserts, HOLDLOCK.
  • OUTPUT $action reports what happened to each row.
  • Update-then-insert in one transaction is a common, simpler alternative.

Next: Transactions and error handling