SQL Server MERGE to Update, Insert, and Delete Rows

MERGE updates, inserts and deletes the rows of a target table so that it matches a source table. For example, one MERGE can update a products table from a new price list. Without MERGE, that job takes a separate UPDATE, INSERT and DELETE.

A MERGE with all three actions has this shape:

MERGE INTO target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET value = s.value
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, value) VALUES (s.id, s.value)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

• MERGE INTO target names the table that changes. In SQL Server, INTO is optional.
• USING source names the table or subquery that holds the new data.
• ON pairs each target row with the source row that has the same key. The two rows of a pair are partners.
• WHEN MATCHED acts on a target row that has a partner in the source. It updates or deletes the row.
• WHEN NOT MATCHED BY TARGET acts on a source row with no partner in the target. It inserts the row into the target.
• WHEN NOT MATCHED BY SOURCE acts on a target row with no partner in the source. It updates or deletes the row.
• The semicolon at the end is required.

A MERGE needs at least one WHEN clause, and the clauses can come in any order. These rules come from Microsoft's MERGE reference for SQL Server 2022.

Create two sample tables with the same columns, a product table and a newer price list, and fill them:

CREATE TABLE products (
  product_id int PRIMARY KEY,
  name       varchar(50),
  price      decimal(10,2)
);

CREATE TABLE products_updated (
  product_id int PRIMARY KEY,
  name       varchar(50),
  price      decimal(10,2)
);

INSERT INTO products VALUES
  (1, 'Banana', 15.00),
  (2, 'Apple',  20.00),
  (3, 'Peach',  19.00);

INSERT INTO products_updated VALUES
  (1, 'Banana', 15.00),
  (2, 'Apple',  25.00),
  (4, 'Milk',   25.00);

products is the target, and products_updated is the source. Apple's price went up, Milk is new, Peach is no longer sold, and Banana is the same in both.

ON pairs the rows by product_id. Each row then takes one WHEN clause:

Before each section below, create and fill the two tables again. Each MERGE then starts from the rows above, not from the result of the MERGE before it.

Every result table comes from a run on PostgreSQL 18.6. PostgreSQL accepts every MERGE here except the ones with OUTPUT, which have no result table. Every SQL Server fact here comes from Microsoft's documentation.

WHEN MATCHED THEN UPDATE

WHEN MATCHED THEN UPDATE copies the source values into each target row that has a partner in the source. For example, you can copy Apple's new price from products_updated into products.

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED THEN
  UPDATE SET name = s.name, price = s.price;

SELECT * FROM products ORDER BY product_id;

Apple's price changed from 20.00 to 25.00, and Banana was rewritten with the same values. Peach has no partner in the source, so MERGE keeps Peach unchanged. Milk isn't added, because no clause of this MERGE inserts rows.

WHEN NOT MATCHED BY TARGET THEN INSERT

WHEN NOT MATCHED BY TARGET THEN INSERT copies each source row with no partner into the target. For example, you can add Milk, the new product in products_updated, to products.

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED THEN
  UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED BY TARGET THEN
  INSERT (product_id, name, price)
  VALUES (s.product_id, s.name, s.price);

SELECT * FROM products ORDER BY product_id;

Milk is now product 4, with the values from products_updated, and Apple was updated as before.

A MERGE has at most one WHEN NOT MATCHED BY TARGET clause. You can also write this clause as WHEN NOT MATCHED, without BY TARGET.

WHEN NOT MATCHED BY SOURCE THEN DELETE

WHEN NOT MATCHED BY SOURCE THEN DELETE removes each target row that has no partner in the source. For example, products_updated no longer lists Peach, so you can delete Peach from products.

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED THEN
  UPDATE SET name = s.name, 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 * FROM products ORDER BY product_id;

Peach is gone, because products_updated has no product 3. The target now holds the same three rows as the source.

Use this clause only when the source lists every row that the target should keep. MERGE deletes every target row that is missing from the source.

OUTPUT and $action

OUTPUT returns one row for each row that the MERGE inserted, updated or deleted. For example, you can log which products a MERGE changed, and how.

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED THEN
  UPDATE SET name = s.name, 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
OUTPUT $action, deleted.name, deleted.price, inserted.name, inserted.price;

The statement returns four rows, in no particular order. The $action column says what happened to each product:

• Banana and Apple: UPDATE, with the values before the change in the deleted columns and the values after it in the inserted columns.
• Peach: DELETE, with NULL in the inserted columns.
• Milk: INSERT, with NULL in the deleted columns.

The MERGE reference calls OUTPUT "the recommended way to query or count rows affected by a MERGE". To store the rows in a table, write INTO and the table name after the column list of the OUTPUT clause. If the target table has an enabled trigger for an action that the MERGE performs, OUTPUT needs INTO.

WHEN MATCHED AND

With an AND condition, WHEN MATCHED acts only on the matched rows where the condition is true. For example, you can update only the products whose name or price changed.

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED AND (t.name <> s.name OR t.price <> s.price) THEN
  UPDATE SET name = s.name, 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
OUTPUT $action, deleted.name, deleted.price, inserted.name, inserted.price;

OUTPUT now returns three rows, for Apple, Peach and Milk. Banana is missing: Banana has the same name and price in both tables, so the AND condition is false for Banana. products ends with the same three rows as under WHEN NOT MATCHED BY SOURCE THEN DELETE.

The other two WHEN clauses take an AND condition too. On WHEN NOT MATCHED BY SOURCE, the condition can read only target columns.

A MERGE may also hold two WHEN MATCHED clauses. The first one needs an AND condition. One of the two must update, and the other must delete.

The comparison with <> misses a change from or to NULL. See NULL in a compared column for the case and its fix.

MERGE and UPDATE with a join

UPDATE with a FROM clause and a join also updates one table from another. For example, this UPDATE changes the same rows as the first MERGE above:

UPDATE products
SET name = s.name, price = s.price
FROM products AS t
JOIN products_updated AS s
  ON t.product_id = s.product_id;

Banana and Apple get the values from products_updated, and Peach stays unchanged. The UPDATE reference shows this form under "Using the UPDATE statement with information from another table".

If you only update one table from another, use UPDATE. According to the MERGE reference, a basic UPDATE scales better than MERGE for an update alone. Use MERGE when one statement has to insert, update and delete, as in the earlier examples.

What goes wrong

The missing semicolon

Without the semicolon at the end of a MERGE, SQL Server raises error 10713. Most other T-SQL statements run without a semicolon at the end, as the syntax conventions note.

Two source rows for one target row

If the source holds two rows with the same key, one target row matches both source rows. When an UPDATE clause meets such a target row, SQL Server returns an error rather than pick one source row. Remove the duplicates from the source first, for example with GROUP BY in a subquery after USING.

UPDATE with a join runs on the same data without an error, and uses one of the source rows. The UPDATE reference calls the result undefined.

NULL in a compared column

The AND condition under WHEN MATCHED AND compares values with <>. A comparison with NULL is never true. So MERGE skips a change from NULL to a value, or from a value to NULL.

Add a product with no price to products, and the same product with a price to products_updated:

INSERT INTO products VALUES (5, 'Tea', NULL);
INSERT INTO products_updated VALUES (5, 'Tea', 4.00);

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED AND (t.name <> s.name OR t.price <> s.price) THEN
  UPDATE SET name = s.name, price = s.price;

SELECT * FROM products WHERE product_id = 5;

Tea still has no price, because NULL <> 4.00 is not true.

Write the AND condition with EXCEPT instead of <>. EXCEPT compares rows and treats two NULLs as equal, as the EXCEPT reference says.

MERGE INTO products AS t
USING products_updated AS s
ON t.product_id = s.product_id
WHEN MATCHED AND EXISTS (SELECT s.name, s.price EXCEPT SELECT t.name, t.price) THEN
  UPDATE SET name = s.name, price = s.price;

SELECT * FROM products WHERE product_id = 5;

The SELECT with EXCEPT returns a row only when the source values differ from the target values, also where one of them is NULL. Tea's target price is NULL and its source price is 4.00. The two prices differ, so EXISTS is true and Tea now costs 4.00.

A filter in ON

Put only the key comparison in ON. The MERGE reference warns against filtering target rows in ON to make the statement faster. Such a filter "can return unexpected and incorrect results".

A target row that the filter removes counts as having no partner in the source. So a WHEN NOT MATCHED BY SOURCE clause deletes that target row. The source row with the same key has no partner either, so a WHEN NOT MATCHED BY TARGET clause inserts the row again.

Put the filter in an AND condition on a WHEN clause instead. For a run of a filter in ON on other tables, see SQL Server MERGE statement.

Source columns in WHEN NOT MATCHED BY SOURCE

Never read a source column in the UPDATE of a WHEN NOT MATCHED BY SOURCE clause. A target row that takes this clause has no source row, and the MERGE fails with an error.

Check a MERGE in DbSchema

DbSchema connects to SQL Server and runs SQL in its SQL Editor, with the rows in a grid under the editor. Before you run a MERGE on real tables, run this query in the SQL Editor. The query shows the pairs that ON makes, so you see which WHEN clause each row will take:

SELECT t.product_id AS target_id, t.price AS target_price,
       s.product_id AS source_id, s.price AS source_price
FROM products AS t
FULL OUTER JOIN products_updated AS s
  ON t.product_id = s.product_id
ORDER BY COALESCE(t.product_id, s.product_id);

A row with both ids takes WHEN MATCHED. Peach has no source_id, so it takes WHEN NOT MATCHED BY SOURCE. Milk has no target_id, so it takes WHEN NOT MATCHED BY TARGET.

Press Editor in the toolbar, choose SQL Editor, paste the query and press Run. Run the MERGE the same way, then check products with a SELECT. If the Commit button blinks, the change is in an open transaction: press Commit to keep it, or Rollback to undo it.

Download DbSchema at https://dbschema.com/download.html, connect it to a copy of your SQL Server database, and try each MERGE above in the SQL Editor. The free Community Edition includes the database connection and the 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