SQL Server MERGE Statement: UPSERT Patterns, WHEN MATCHED, and Safer Alternatives
For a SQL Server developer who loads a staging table from a feed and has to make the target table match it.
On this page
A feed lands in a staging table overnight, and by morning the target table has to match it. Changed rows have to be updated, new rows inserted, and rows missing from the feed deleted. The SQL Server MERGE statement does all three in one statement. It joins the target to the source once, and each row takes the branch that fits how it matched:
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;
What MERGE does with each row
ON pairs each target row with the source row that has the same key. Every row then falls into one of three groups, and each group has a branch of its own:
| Branch | Rows it receives | Actions it allows | Clauses per statement |
|---|---|---|---|
WHEN MATCHED | in both tables | UPDATE or DELETE | up to two |
WHEN NOT MATCHED BY TARGET | only in the source | INSERT | one |
WHEN NOT MATCHED BY SOURCE | only in the target | UPDATE or DELETE | up to two |
The limits come from Microsoft's MERGE reference. A statement needs at least one branch, and the branches can come in any order. WHEN NOT MATCHED alone means WHEN NOT MATCHED BY TARGET. WHEN MATCHED and WHEN NOT MATCHED BY SOURCE may each appear twice. Then the first of the two clauses needs an AND condition, and one clause updates while the other deletes. A row of a group that has no branch in the statement stays as it is. Without WHEN NOT MATCHED BY SOURCE, for example, the statement deletes no row.
To learn the branches one at a time, read SQL Server MERGE to Update, Insert, and Delete Rows, which runs a query for each. Below, the whole statement runs on SQL Server, the way a sync job runs it.
Two jobs call for MERGE: a scheduled load into a data warehouse, and an incremental update that applies a table of changes. Every edition of SQL Server 2022 has MERGE, Express included, according to the editions and features list.
Run a MERGE in sqlcmd
sqlcmd runs T-SQL from the command prompt. Connect it to the database you want to change:
sqlcmd -S ServerName -d DatabaseName -U UserName
Without -P, sqlcmd asks for the password. The sqlcmd documentation calls a password on the command line insecure. Without -U as well, sqlcmd signs in with your Windows account. If you have no server yet, start with creating a SQL Server database.
Every result and error in this article was copied from a run on SQL Server 2022 (CU24, version 16.0.4245.2), through sqlcmd version 18.
Create the two tables:
CREATE TABLE dbo.Products (
ProductID int PRIMARY KEY,
ProductName nvarchar(50) NOT NULL,
Price decimal(10,2) NOT NULL
);
CREATE TABLE dbo.UpdatedProducts (
ProductID int PRIMARY KEY,
ProductName nvarchar(50) NOT NULL,
Price decimal(10,2) NOT NULL
);
INSERT INTO dbo.Products VALUES
(1, 'Apple', 1.00),
(2, 'Banana', 0.50),
(3, 'Cherry', 2.00);
INSERT INTO dbo.UpdatedProducts VALUES
(2, 'Banana', 0.60),
(3, 'Cherry', 2.10),
(4, 'Durian', 3.00);
GO
sqlcmd sends the statements to the server when it reads GO on a line of its own. UpdatedProducts is the feed. Banana and Cherry cost more in it, Durian is new, and Apple is missing. This MERGE makes Products match the feed:
MERGE dbo.Products AS T
USING dbo.UpdatedProducts AS S
ON T.ProductID = S.ProductID
WHEN MATCHED 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;
GO
(4 rows affected)
The count covers the whole statement: Banana and Cherry updated, Apple deleted, Durian inserted.
SELECT ProductID, ProductName, Price FROM dbo.Products ORDER BY ProductID;
| ProductID | ProductName | Price |
|---|---|---|
| 2 | Banana | 0.60 |
| 3 | Cherry | 2.10 |
| 4 | Durian | 3.00 |
Run the same statement twice
Run the same MERGE again, with nothing changed in the feed, and sqlcmd reports (3 rows affected). WHEN MATCHED updates every matched row, even where the values are already equal, and each update counts toward the total. Add an AND condition to WHEN MATCHED. WHEN MATCHED then updates only the rows that differ:
MERGE dbo.Products AS T
USING dbo.UpdatedProducts 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;
GO
(0 rows affected)
The <> comparison works here because both columns are NOT NULL. Where a column allows NULL, <> misses a change to or from NULL. For such a column, write the condition with EXCEPT, which treats two NULLs as equal: AND EXISTS (SELECT S.ProductName, S.Price EXCEPT SELECT T.ProductName, T.Price). For a step-by-step run of that case, see NULL in a compared column.
Keep the rows the feed doesn't carry
Leave out WHEN NOT MATCHED BY SOURCE when the feed carries only a slice of the table, such as the products of one supplier. Without that branch, the first MERGE above would have kept Apple, and no run of the statement deletes a row.
See which row took which action with OUTPUT
(4 rows affected) is one total, and so is @@ROWCOUNT after a MERGE. The OUTPUT clause returns one row for each changed row instead, and the MERGE reference calls it "the recommended way to query or count rows affected by a MERGE". Its $action column holds INSERT, UPDATE or DELETE. The deleted and inserted prefixes give a column's value before and after the change.
Put Products back where it started, then run the merge with an audit table that stores the OUTPUT rows:
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, 'Apple', 1.00),
(2, 'Banana', 0.50),
(3, 'Cherry', 2.00);
MERGE dbo.Products AS T
USING dbo.UpdatedProducts 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
OUTPUT
$action,
COALESCE(inserted.ProductID, deleted.ProductID),
deleted.Price,
inserted.Price
INTO dbo.ProductMergeAudit (MergeAction, ProductID, OldPrice, NewPrice);
GO
COALESCE takes the key from whichever side has one. A deleted row has no inserted values, and an inserted row has no deleted values.
SELECT MergeAction, ProductID, OldPrice, NewPrice
FROM dbo.ProductMergeAudit ORDER BY ProductID;
| MergeAction | ProductID | OldPrice | NewPrice |
|---|---|---|---|
| DELETE | 1 | 1.00 | NULL |
| UPDATE | 2 | 0.50 | 0.60 |
| UPDATE | 3 | 2.00 | 2.10 |
| INSERT | 4 | NULL | 3.00 |
OUTPUT returns its rows in no particular order, so the query sorts them. On a table without triggers, leave out the INTO line, and the same rows come back as a result set. That's handy while you test.
Rules and errors to check before production
A MERGE fails, or does something you didn't mean, when one of these rules is missed:
- A
MERGEhas to end with a semicolon. Without one, SQL Server raises error 10713,A MERGE statement must be terminated by a semi-colon (;). - A target row may match only one source row when its branch updates or deletes it. Two source rows with the same key raise error 8672,
The MERGE statement attempted to UPDATE or DELETE the same row more than once.Group or deduplicate the source first. - The
WHEN NOT MATCHED BY SOURCEbranch can't read source columns, because it has no source row.SET T.Price = S.Pricethere fails with error 4104,The multi-part identifier "S.Price" could not be bound.A bare column name that only the source has fails with error 207 instead. - The statement needs
SELECTpermission on the source andINSERT,UPDATEorDELETEpermission on the target.
Keep ON for the match alone
The MERGE reference warns against narrowing the target rows in ON to make the statement faster, because doing so "can return unexpected and incorrect results". After the merge with OUTPUT, Products holds Banana at 0.60, Cherry at 2.10 and Durian at 3.00. Say you want to touch only the products priced over 1.00, and you write that into ON:
MERGE dbo.Products AS T
USING dbo.UpdatedProducts AS S
ON T.ProductID = S.ProductID AND T.Price > 1.00
WHEN MATCHED 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);
Violation of PRIMARY KEY constraint 'PK__Products__B40CC6EDEDA941D8'. Cannot insert duplicate key in object 'dbo.Products'. The duplicate key value is (2).
Banana's target row costs 0.60, so the filter takes it out of the match. Its source row then counts as not matched by the target, and MERGE tries to insert product 2 a second time:
Add a WHEN NOT MATCHED BY SOURCE THEN DELETE branch, and the same filter raises no error. MERGE deletes Banana and inserts it again. The table ends up right, but OUTPUT, an audit table and any trigger record a delete and an insert for a product that stayed in the feed. Put the narrowing condition on the branch instead, as WHEN MATCHED AND T.Price > 1.00.
What MERGE does to triggers
SQL Server fires the target's AFTER triggers for every insert, update and delete that the MERGE performs. It doesn't guarantee which action's triggers fire first. Inside any of them, @@ROWCOUNT reports the total for the whole statement. In the run, an AFTER INSERT trigger on Products logged @@ROWCOUNT. During the first MERGE above, which inserted only Durian, the trigger logged 4.
So a trigger that branches on @@ROWCOUNT behaves differently under MERGE than under a plain INSERT. If the target has an INSTEAD OF trigger for one action of the statement, it needs one for every action that the statement specifies. If the target has an enabled trigger for an action that the MERGE performs, OUTPUT works only with INTO.
MERGE or separate statements
The same change can be written three ways:
| Pattern | Statements | Removes rows the source dropped | Fits |
|---|---|---|---|
MERGE | one | yes, with the source branch | staging to target sync |
UPDATE, then INSERT if no row changed | two | no | a single key on a hot path |
separate INSERT, UPDATE and DELETE | three | yes | large batches under heavy concurrency |
MERGE states the join once, and the delete is a branch rather than one more statement to keep in step. According to the MERGE reference, one statement can also read the source and target fewer times, given an index on the join columns of both tables.
The cost is locking. The reference says that at scale, MERGE "might introduce complicated concurrency issues". Where heavy concurrency is expected, separate INSERT, UPDATE and DELETE logic "might perform better, with less blocking". A nightly load that runs while nothing else writes to the table suits MERGE. If many sessions write to the table at once, test the MERGE under that load first.
Upsert one row
For a single key, the source can be one row of values rather than a table:
MERGE dbo.CustomerBalances WITH (HOLDLOCK) AS T
USING (VALUES (@CustomerID, @Balance)) AS S (CustomerID, Balance)
ON T.CustomerID = S.CustomerID
WHEN MATCHED THEN
UPDATE SET T.Balance = S.Balance
WHEN NOT MATCHED THEN
INSERT (CustomerID, Balance) VALUES (S.CustomerID, S.Balance);
HOLDLOCK matters when many sessions upsert the same keys. It's a synonym for the SERIALIZABLE isolation level. The MERGE reference says that where the same keys are both inserted and updated, HOLDLOCK can prevent unique key violations.
The explicit form does the same in two statements, which a reviewer can check one at a time. It names its locks itself:
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;
Either way, the first call for a customer inserts the row, and every later call updates it. SQL Server Transactions explains the transaction around the explicit form.
Run and check a MERGE in DbSchema
A merge job copies values from one table into another, and the MERGE doesn't show the column types of either table. DbSchema connects to SQL Server and draws the tables on a diagram, where you compare the columns of UpdatedProducts and Products before the statement runs. In its SQL Editor, you can then run the MERGE and undo it.
- In DbSchema, choose File → New Model, keep Connect to Database selected, and click SQL Server.
- Fill in the connection dialog and press Connect. DbSchema downloads the SQL Server JDBC driver the first time.
- In Reverse Engineer the Database, tick the schema and press Load. DbSchema draws its tables on a diagram.
- Press Editor in the toolbar and choose SQL Editor.
- Type the first
MERGEof the sqlcmd section, up to its semicolon, and press Run. - Check the rows with a
SELECT. If the Commit button blinks, the change waits in an open transaction: press Commit to keep it, or Rollback to undo it.
The MERGE changes rows in the database only. The DbSchema design model holds the structure of the tables, so no data change reaches it. A table that you create with SQL, such as the audit table above, reaches the model when you press Refresh in the DbSchema toolbar, which needs DbSchema Pro.
To try the statements above on your own tables, download DbSchema at https://dbschema.com/download.html. Connect it to a copy of your SQL Server database, and run them in the SQL Editor. Connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition. Saving the model to a file and refreshing it from the database are in DbSchema Pro.

