SQL Server MERGE to Update, Insert, and Delete Rows
For SQL users who already write joins and multi-statement loads against SQL Server; the MERGE clauses and the errors they raise are covered here.
On this page
Every night a staging table arrives holding what a reference table should look like in the morning. Some of its rows are new, some carry changed values, and some rows in the reference table are missing from it because they were retired. SQL Server's MERGE settles all three against one join: you name a target, a source, and the condition that pairs their rows, and then say what to do when a pair matches, when a source row has no partner, and when a target row has no partner.
A MERGE example with two product tables
The examples run on SQL Server 2022 against two tables with the same shape, one holding the current catalog and one holding the version that should replace it:
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);
GO
INSERT INTO products VALUES
(1, 'Banana', 15.00),
(2, 'Apple', 20.00),
(3, 'Chocolate', 20.00),
(4, 'Cake', 40.00),
(5, 'Peach', 19.00);
GO
CREATE TABLE products_updated (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);
GO
INSERT INTO products_updated VALUES
(1, 'Banana', 15.00),
(2, 'Apple', 25.00),
(3, 'Milk', 25.00),
(4, 'Cake', 60.00);
GO
Two prices changed, product 3 was renamed, and product 5 is gone from the new catalog. One statement applies all of it:
MERGE products AS t
USING products_updated AS s
ON (s.product_id = t.product_id)
WHEN MATCHED
THEN UPDATE SET
t.name = s.name,
t.price = s.price
WHEN NOT MATCHED BY TARGET
THEN INSERT (product_id, name, price)
VALUES (s.product_id, s.name, s.price)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
SELECT product_id, name, price FROM products ORDER BY product_id;
| product_id | name | price |
|---|---|---|
| 1 | Banana | 15.00 |
| 2 | Apple | 25.00 |
| 3 | Milk | 25.00 |
| 4 | Cake | 60.00 |
Product 1 was rewritten with values identical to the ones it already had, so it reads as unchanged. Products 2, 3 and 4 took the new name and price. Product 5 matched no source row and was deleted. Nothing was inserted here, because every product_id in the source already existed in the target.
What each MATCHED clause does
The ON clause is the join. It decides which target row pairs with which source row, which is why a primary key or a unique index is the column to put there. Microsoft's MERGE reference adds a caution worth reading before you extend that condition: name only the columns used for matching, and don't try to speed the statement up by filtering target rows in ON, because it can return incorrect results.
WHEN MATCHED covers the pairs. Its action is UPDATE or DELETE, and a statement can carry two such clauses, in which case the first needs an AND condition, one clause updates and the other deletes.
WHEN NOT MATCHED BY TARGET covers a source row with no partner, and its action is INSERT. A statement can have only one of these. Writing it as WHEN NOT MATCHED, as the shorter form, means the same thing.
WHEN NOT MATCHED BY SOURCE covers a target row with no partner, and its action is UPDATE or DELETE. Only columns from the target table can be referenced in its condition: the source row does not exist, so reading a source column there returns error 207, "Invalid column name". At least one of the three clauses has to be present, and they can appear in any order.
To see what the statement actually did rather than infer it from a later SELECT, add an OUTPUT clause. The $action column is an nvarchar(10) that reads INSERT, UPDATE or DELETE for each row MERGE touched, and Microsoft calls OUTPUT the recommended way to query or count the rows a MERGE affected:
MERGE products AS t
USING products_updated AS s
ON (s.product_id = t.product_id)
WHEN MATCHED
THEN UPDATE SET t.price = s.price
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, inserted.product_id, deleted.product_id;
The rules that make a MERGE statement fail
MERGE needs a semicolon as its statement terminator. Leave it off and SQL Server raises error 10713, which is a surprise the first time, because the statements around it in the same script do not need one.
The join has to identify at most one source row per target row. When UPDATE is specified in a matched clause and more than one row of the source matches a row in the target, SQL Server returns an error: the statement can neither update the same row more than once, nor update and delete the same row.
Two more constraints come from the same page. IGNORE_DUP_KEY is ignored on the target's unique indexes for the duration of the statement, and every insert, update and delete MERGE performs is still subject to the constraints defined on the target, cascading foreign keys included.
Try a MERGE on a copy of the target before it goes anywhere near a nightly job. DbSchema connects to your SQL Server database and runs both the statement and the SELECT after it in the same window, which is the quickest way to see what the clauses did. Connecting and the SQL editor are in the free Community Edition, at https://dbschema.com/download.html.
