SQL Server MERGE Statement: UPSERT Patterns, WHEN MATCHED, and Safer Alternatives

A feed lands in a staging table overnight, and by morning the target table has to look like it: changed rows updated, new rows inserted, rows that vanished from the feed gone. MERGE does all three in one statement. You join the target to the source once, and every row takes the branch that fits how it matched.

What the SQL Server MERGE statement does

MERGE joins a target table to a source and then runs an insert, an update, or a delete on each row of the target, according to the join. Rows that matched are handled by a WHEN MATCHED branch, source rows with no match in the target by WHEN NOT MATCHED BY TARGET, and target rows the source no longer contains by WHEN NOT MATCHED BY SOURCE. At least one branch has to be there, and the MERGE documentation sets the counts: at most two WHEN MATCHED clauses (one updating, one deleting, the first carrying an AND condition), exactly one WHEN NOT MATCHED BY TARGET, and at most two WHEN NOT MATCHED BY SOURCE.

One piece of syntax is easy to leave off. The statement has to end with a semicolon, and SQL Server raises error 10713 when it doesn't.

Basic MERGE syntax

MERGE dbo.TargetTable AS T
USING dbo.SourceTable AS S
ON T.BusinessKey = S.BusinessKey
WHEN MATCHED THEN
    UPDATE SET T.ValueCol = S.ValueCol
WHEN NOT MATCHED BY TARGET THEN
    INSERT (BusinessKey, ValueCol)
    VALUES (S.BusinessKey, S.ValueCol)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

The ON clause decides which rows match, and nothing else. Microsoft's documentation warns against the tempting shortcut of filtering target rows there to make the statement faster, because a MERGE written that way can return incorrect results. A condition that narrows which rows you want to touch belongs on the branch instead, written as WHEN MATCHED AND ....

The match also has to be unambiguous. If two source rows match the same target row and the branch specifies UPDATE, SQL Server returns an error, because MERGE can't update the same row more than once.

MERGE vs other UPSERT patterns

Write MERGE when a whole set has to be reconciled against another set: the join is stated once, the three actions read together, and the delete branch is a clause rather than a fourth statement to keep in step. Write the separate statements when concurrency is the constraint. The documentation is explicit that MERGE locks differently from discrete statements, that it can raise complicated concurrency problems at scale, and that separate INSERT, UPDATE and DELETE logic may block less where heavy concurrency is expected. Whichever you pick, test it against the concurrency you actually expect before it runs on production.

If your merge flow also fires business logic in triggers, read the FAQ below on what MERGE does to them.

MERGE example for table synchronization

The target holds the current catalog, the staging table holds tonight's feed:

CREATE TABLE dbo.Products (
    ProductID   int PRIMARY KEY,
    ProductName nvarchar(100) NOT NULL,
    Price       decimal(10,2) NOT NULL
);

CREATE TABLE dbo.ProductsStage (
    ProductID   int PRIMARY KEY,
    ProductName nvarchar(100) NOT NULL,
    Price       decimal(10,2) NOT NULL
);

INSERT INTO dbo.Products VALUES
    (1, 'Keyboard', 40.00),
    (2, 'Mouse', 12.50),
    (3, 'Webcam', 55.00);

INSERT INTO dbo.ProductsStage VALUES
    (1, 'Keyboard', 44.00),
    (2, 'Mouse', 12.50),
    (4, 'Headset', 30.00);

The keyboard changed price, the mouse didn't, the webcam left the feed, and the headset is new. One statement covers the four cases:

MERGE dbo.Products AS T
USING dbo.ProductsStage AS S
ON T.ProductID = S.ProductID
WHEN MATCHED AND (T.ProductName <> S.ProductName OR T.Price <> S.Price) THEN
    UPDATE SET
        T.ProductName = S.ProductName,
        T.Price = S.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (S.ProductID, S.ProductName, S.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SELECT ProductID, ProductName, Price FROM dbo.Products ORDER BY ProductID;

The mouse row was matched and left alone, because the AND condition on the WHEN MATCHED branch found nothing different to write. Drop the WHEN NOT MATCHED BY SOURCE branch and the webcam stays: the target then only grows and changes, which is what you want when the feed carries a slice of the catalog rather than all of it.

Note what the source branch may read. Columns of the source table aren't accessible in a WHEN NOT MATCHED BY SOURCE clause, because no source row is in hand; referring to one returns error 207, invalid column name.

Capture actions with OUTPUT

@@ROWCOUNT after a MERGE returns one number covering all three actions together, and never says which row took which action. The OUTPUT clause gives a row per changed row, and the documentation calls it "the recommended way to query or count rows affected by a MERGE". Its $action column is an nvarchar(10) holding INSERT, UPDATE, or DELETE for that row.

The merge in the previous section already changed dbo.Products, so the block below puts the three loaded rows back before the audited statement runs:

CREATE TABLE dbo.ProductMergeAudit (
    MergeAction nvarchar(10),
    ProductID   int,
    OldPrice    decimal(10,2) NULL,
    NewPrice    decimal(10,2) NULL
);

DELETE FROM dbo.Products;
INSERT INTO dbo.Products VALUES
    (1, 'Keyboard', 40.00),
    (2, 'Mouse', 12.50),
    (3, 'Webcam', 55.00);

MERGE dbo.Products AS T
USING dbo.ProductsStage AS S
ON T.ProductID = S.ProductID
WHEN MATCHED AND T.Price <> S.Price THEN
    UPDATE SET T.Price = S.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (S.ProductID, S.ProductName, S.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $action,
    COALESCE(inserted.ProductID, deleted.ProductID),
    deleted.Price,
    inserted.Price
INTO dbo.ProductMergeAudit (MergeAction, ProductID, OldPrice, NewPrice);

The audit table then holds one row per action the statement took:

SELECT MergeAction, ProductID, OldPrice, NewPrice
FROM dbo.ProductMergeAudit ORDER BY ProductID;

OUTPUT returns its rows in no particular order, so sort them when you read them back, as the query above does with ORDER BY.

Safer alternative for critical UPSERT logic

For one business key on a path that many sessions hit at once, the explicit form is easier to reason about, and it lets you name the locking yourself:

BEGIN TRANSACTION;

UPDATE dbo.CustomerBalances WITH (UPDLOCK, HOLDLOCK)
SET Balance = @Balance
WHERE CustomerID = @CustomerID;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.CustomerBalances (CustomerID, Balance)
    VALUES (@CustomerID, @Balance);
END;

COMMIT TRANSACTION;

HOLDLOCK is the part that matters here. It is a synonym for the SERIALIZABLE isolation level, and the MERGE documentation names it as the way to prevent unique key violations where the same key can be inserted and updated concurrently, whichever statement you write. The pattern above is longer than a MERGE, and it reads as two ordinary statements that a reviewer can check one at a time.

For related examples, see Use MERGE to Update Tables and SQL Server Transactions.

A merge job in the DbSchema diagram and SQL Editor

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

A merge job touches three tables at once, and reading the statement tells you nothing about the shape of the two it doesn't declare. DbSchema connects to SQL Server through the SQL Server JDBC driver, reverse-engineers the schema, and draws the target, the staging table, and the audit table on one diagram, where you can check that the columns you are copying between them line up.

Write the statement in the DbSchema SQL Editor. MERGE ends in a semicolon, so Execute Query runs it as the statement at the cursor, and the SQL History pane keeps every statement you ran in the session, which is where you find the exact text of last night's merge. Running it there writes to the live database. The design model file changes separately: Schema → Refresh Schema from Database pulls the current database state back into the model, and Schema → Synchronize Model with Database generates the migration statements for structural changes you made in the model, such as the audit table you just added.

Decide first whether you are reconciling a set or updating a row, then write MERGE or the explicit transaction accordingly, and put an OUTPUT clause on whichever one goes to production. To try the statements above against your own tables, download DbSchema at https://dbschema.com/download.html and connect to your SQL Server database: the SQL Editor and the diagrams are in the free Community Edition, while saving the design as a .dbs file and schema synchronization are in Pro.

FAQ

Does MERGE work well with triggers?

SQL Server fires the AFTER triggers defined on the target table for every insert, update, and delete the MERGE performs, but doesn't guarantee which action fires its triggers first. @@ROWCOUNT read inside any of those triggers reports the total number of rows the MERGE affected, not the number for that action, so a trigger that branches on @@ROWCOUNT behaves differently under MERGE than under a plain INSERT.

Is MERGE available in every edition of SQL Server?

The feature list for SQL Server 2022 shows MERGE and upsert capabilities in Enterprise, Standard, Web, and both Express editions, so a merge job written on a developer machine runs on the edition your customer bought.