MERGE Statement

MERGE performs INSERT, UPDATE, and DELETE against a target table in a single statement based on a match condition with a source. It is the canonical T-SQL UPSERT, but it has well-documented gotchas — in many production scenarios a transactional INSERT/UPDATE pair is safer.

Syntax

MERGE INTO target_table AS t
USING source AS s
   ON  t.key = s.key
WHEN MATCHED [AND condition] THEN
    UPDATE SET t.col = s.col, ...
WHEN NOT MATCHED [BY TARGET] [AND condition] THEN
    INSERT (cols) VALUES (s.cols)
WHEN NOT MATCHED BY SOURCE [AND condition] THEN
    DELETE
OUTPUT $action, inserted.*, deleted.*;

The three branches:

Branch	Fires when
WHEN MATCHED	A row exists in both source and target
WHEN NOT MATCHED [BY TARGET]	Row in source, not in target — INSERT
WHEN NOT MATCHED BY SOURCE	Row in target, not in source — typically DELETE

Classic UPSERT

MERGE INTO dbo.employees AS t
USING (
    VALUES
        (100, 'Steven',   'King',     24000),
        (101, 'Neena',    'Kochhar',  17000),
        (999, 'New',      'Person',    7500)
) AS s (employee_id, first_name, last_name, salary)
   ON  t.employee_id = s.employee_id
WHEN MATCHED AND (
        t.first_name <> s.first_name OR
        t.last_name  <> s.last_name  OR
        t.salary     <> s.salary
     ) THEN
    UPDATE SET
        t.first_name = s.first_name,
        t.last_name  = s.last_name,
        t.salary     = s.salary
WHEN NOT MATCHED BY TARGET THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (s.employee_id, s.first_name, s.last_name, s.salary)
OUTPUT $action, inserted.employee_id, inserted.salary, deleted.salary;

The $action pseudo-column tells you which branch fired for each row: 'INSERT', 'UPDATE', or 'DELETE'.

Synchronizing Two Tables

The full three-branch use case — make a target match a source completely:

MERGE INTO dbo.dim_customer AS t
USING staging.customer       AS s
   ON  t.customer_id = s.customer_id
WHEN MATCHED AND (
        t.customer_name <> s.customer_name OR
        t.region        <> s.region        OR
        t.email         <> s.email
     ) THEN
    UPDATE SET
        t.customer_name = s.customer_name,
        t.region        = s.region,
        t.email         = s.email,
        t.updated_at    = SYSDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (customer_id, customer_name, region, email)
    VALUES (s.customer_id, s.customer_name, s.region, s.email)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.customer_id, deleted.customer_id;

OUTPUT $action

DECLARE @log TABLE (action NVARCHAR(10), id INT);

MERGE INTO dbo.products AS t
USING staging.products AS s
   ON t.sku = s.sku
WHEN MATCHED THEN
    UPDATE SET t.list_price = s.list_price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (sku, name, list_price) VALUES (s.sku, s.name, s.list_price)
OUTPUT $action, COALESCE(inserted.product_id, deleted.product_id) INTO @log;

SELECT action, COUNT(*) AS row_count
FROM   @log
GROUP BY action;

The Semicolon Gotcha

MERGE must end with a semicolon. Without it, you get parse errors that look mysterious because they often complain about the next batch.

-- Always terminate MERGE with a semicolon
MERGE INTO ... ;

Known Issues — Aaron Bertrand's Caveats

MERGE has a long, painful bug history. Aaron Bertrand and Paul White have catalogued many. Highlights:

• Race conditions — under READ COMMITTED isolation, two concurrent MERGE statements can both decide a row "doesn't exist" and both INSERT, causing primary key violations. MERGE does not automatically take a lock that prevents this. Use HOLDLOCK/SERIALIZABLE or sp_getapplock.
• Filtered indexes & PK violations — open Microsoft bugs around incorrect handling.
• Triggers fire once for the whole MERGE — a single AFTER INSERT, UPDATE, DELETE trigger sees a mix of operations in inserted/deleted.
• Foreign key cascades can produce wrong results in older SQL Server versions.

-- The recommended safe form for upserts under concurrency
MERGE INTO dbo.config WITH (HOLDLOCK) AS t  -- HOLDLOCK = SERIALIZABLE on this stmt
USING (SELECT @key AS k, @value AS v) AS s
   ON t.k = s.k
WHEN MATCHED THEN UPDATE SET t.v = s.v
WHEN NOT MATCHED THEN INSERT (k, v) VALUES (s.k, s.v);

For high-concurrency UPSERTs, many practitioners — including Bertrand — recommend a plain transactional pattern instead:

BEGIN TRAN;

UPDATE dbo.config WITH (UPDLOCK, SERIALIZABLE)
SET    v = @value
WHERE  k = @key;

IF @@ROWCOUNT = 0
    INSERT INTO dbo.config (k, v) VALUES (@key, @value);

COMMIT;

Best Practices

• Always end MERGE with a semicolon.
• Use WITH (HOLDLOCK) on the target to avoid the concurrent-insert race.
• Add a CHANGED-rows guard (WHEN MATCHED AND (a <> b OR c <> d)) so unchanged rows aren't needlessly updated — saves log churn and trigger fires.
• Test triggers carefully — they see all three operations folded together.
• For simple two-statement upserts under heavy concurrency, prefer plain UPDATE + IF @@ROWCOUNT = 0 INSERT pattern.

Pitfalls

• Cannot reference the same target table in the source without an explicit subquery.
• Source must produce at most one row per target match, or you get error 8672 ("MERGE statement attempted to UPDATE or DELETE the same row more than once").
• OUTPUT exposes inserted and deleted; rows that were INSERTed have NULL deleted.*, and vice versa for DELETE.

Summary

• MERGE combines INSERT, UPDATE, and DELETE in a single statement.
• $action in OUTPUT reveals which branch fired per row.
• Always terminate with a semicolon, and use HOLDLOCK (or a transactional pattern) to avoid concurrent-insert races.
• For high-concurrency UPSERTs, a plain UPDATE-then-INSERT transaction is often safer.