Lesson 35 of 54 18 minAdvanced

MERGE, upserts and the OUTPUT clause

Insert-or-update in one statement: the two-statement upsert, MERGE with its three WHEN clauses, $action in OUTPUT, saving OUTPUT into a table, a full sync with NOT MATCHED BY SOURCE, and the traps: duplicate source rows, unscoped deletes and concurrency.

A supplier sends a price list each week. Some products are already in our table and need their price and stock updated; some are new and must be inserted. “Update if it exists, insert if it doesn’t” is called an upsert. The feed for this lesson:

Query.sql

1CREATE TABLE dbo.SupplierFeed (2    Name       nvarchar(60)  NOT NULL,3    CategoryId int           NOT NULL,4    Price      decimal(10,2) NOT NULL,5    Stock      int           NOT NULL,6    Unit       nvarchar(10)  NOT NULL7);8INSERT INTO dbo.SupplierFeed VALUES9    (N'Claw hammer',  1, 19.50,  55, N'each'),   -- dearer, more stock10    (N'Cement 25 kg', 2,  7.20, 300, N'bag'),    -- unchanged11    (N'Sand 25 kg',   2,  4.30, 260, N'bag'),    -- dearer, more stock12    (N'Garden spade', 1, 24.00,  15, N'each'),   -- new13    (N'Pipe cutter',  7, 16.50,  10, N'each');   -- new

The upsert in two statements

Without MERGE, an upsert is an UPDATE of the rows that match, then an INSERT of the rows that do not:

Query.sql

1BEGIN TRANSACTION;2 3UPDATE p4SET    p.Price = f.Price, p.Stock = f.Stock5FROM   dbo.Products AS p6JOIN   dbo.SupplierFeed AS f ON f.Name = p.Name7WHERE  p.Price <> f.Price OR p.Stock <> f.Stock;8 9INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)10SELECT f.Name, f.CategoryId, f.Price, f.Stock, f.Unit, '2024-07-01'11FROM   dbo.SupplierFeed AS f12WHERE  NOT EXISTS (SELECT 1 FROM dbo.Products AS p WHERE p.Name = f.Name);13 14COMMIT;15 16SELECT ProductId, Name, Price, Stock17FROM dbo.Products18WHERE ProductId IN (1, 4, 5) OR ProductId > 1219ORDER BY ProductId;

Output

ProductId  Name          Price  Stock
---------  ------------  -----  -----
        1  Claw hammer   19.50     55
        4  Cement 25 kg   7.20    300
        5  Sand 25 kg     4.30    260
       13  Garden spade  24.00     15
       14  Pipe cutter   16.50     10

This is clear and works well. The WHERE on the UPDATE skips rows that would not change, such as the cement. The transaction makes the pair all-or-nothing.

MERGE: one statement

MERGE compares a target table with a source on a join condition, and says what to do in each case:

Query.sql

1MERGE dbo.Products AS t2USING dbo.SupplierFeed AS s3   ON t.Name = s.Name4WHEN MATCHED AND (t.Price <> s.Price OR t.Stock <> s.Stock) THEN5    UPDATE SET t.Price = s.Price, t.Stock = s.Stock6WHEN NOT MATCHED BY TARGET THEN7    INSERT (Name, CategoryId, Price, Stock, Unit, AddedOn)8    VALUES (s.Name, s.CategoryId, s.Price, s.Stock, s.Unit, '2024-07-01')9OUTPUT $action AS Action, inserted.ProductId, inserted.Name,10       deleted.Price AS OldPrice, inserted.Price AS NewPrice;

Output

Action  ProductId  Name          OldPrice  NewPrice
------  ---------  ------------  --------  --------
INSERT         13  Garden spade      NULL     24.00
INSERT         14  Pipe cutter       NULL     16.50
UPDATE          1  Claw hammer      18.50     19.50
UPDATE          5  Sand 25 kg        4.10      4.30

Clause	The row is	Allowed actions
`WHEN MATCHED [AND …]`	in both target and source	UPDATE or DELETE
`WHEN NOT MATCHED [BY TARGET] [AND …]`	in the source only	INSERT
`WHEN NOT MATCHED BY SOURCE [AND …]`	in the target only	UPDATE or DELETE

In OUTPUT, $action says what happened to each row: INSERT, UPDATE or DELETE. deleted. holds the values before and inserted. the values after, so OldPrice is NULL for the inserts. The cement matched, but its AND test was false, so nothing happened to it and it is not in the output.

Interactive

Syncing the branch stock

WHEN MATCHED AND t.Qty <> s.Qty THEN UPDATEWHEN NOT MATCHED BY TARGET THEN INSERTWHEN NOT MATCHED BY SOURCE THEN DELETE Add a second count for product 1: (1, 36)

Target: dbo.BranchStock

	ProductId	Qty
1	1	40
2	3	65
3	9	5
4	12	0

Source: dbo.Stocktake

	ProductId	Qty
1	1	38
2	3	65
3	5	20
4	12	6

What happens to each product

1Claw hammerUPDATEmatched, 40 → 38
3Tape measure 8 mno changematched, but 65 = 65, so the AND test is false
5Sand 25 kgINSERTin the source only
9Wall plugs (100)DELETEin the target only: not counted
12Safety gogglesUPDATEmatched, 0 → 6

OUTPUT

	Action	ProductId	OldQty	NewQty
1	UPDATE	1	40	38
2	INSERT	5	NULL	20
3	DELETE	9	5	NULL
4	UPDATE	12	0	6

Target after

	ProductId	Qty
1	1	38
2	3	65
3	5	20
4	12	6

All three clauses: the branch table now matches the stocktake exactly. Product 3 matched but did not change, so it is not in OUTPUT. The order of OUTPUT rows is not guaranteed.

OUTPUT INTO: keep a record

OUTPUT … INTO writes the output rows into a table (or table variable) instead of returning them. It works on INSERT, UPDATE, DELETE and MERGE, and makes a change log in the same statement. Here a 10% price rise on Paint is logged:

Query.sql

1DECLARE @log TABLE (ProductId int, OldPrice decimal(10,2), NewPrice decimal(10,2));2 3UPDATE dbo.Products4SET Price = CAST(Price * 1.10 AS decimal(10,2))5OUTPUT inserted.ProductId, deleted.Price, inserted.Price INTO @log6WHERE CategoryId = 5;7 8SELECT l.ProductId, p.Name, l.OldPrice, l.NewPrice, l.NewPrice - l.OldPrice AS Rise9FROM @log AS l10JOIN dbo.Products AS p ON p.ProductId = l.ProductId11ORDER BY l.ProductId;

Output

ProductId  Name                OldPrice  NewPrice  Rise
---------  ------------------  --------  --------  ----
       10  Masonry paint 10 L     54.00     59.40  5.40
       11  Paint roller set       12.50     13.75  1.25

A full sync: NOT MATCHED BY SOURCE

The third clause handles rows that are in the target but missing from the source. It turns MERGE into a sync that makes the target match the source exactly. A branch keeps its own stock table, and a stocktake has just counted the shelves:

Query.sql

1CREATE TABLE dbo.BranchStock (ProductId int PRIMARY KEY, Qty int NOT NULL);2INSERT INTO dbo.BranchStock VALUES (1, 40), (3, 65), (9, 5), (12, 0);3 4CREATE TABLE dbo.Stocktake (ProductId int NOT NULL, Qty int NOT NULL);5INSERT INTO dbo.Stocktake VALUES (1, 38), (3, 65), (5, 20), (12, 6);

Query.sql

1MERGE dbo.BranchStock AS t2USING dbo.Stocktake AS s3   ON t.ProductId = s.ProductId4WHEN MATCHED AND t.Qty <> s.Qty THEN5    UPDATE SET t.Qty = s.Qty6WHEN NOT MATCHED BY TARGET THEN7    INSERT (ProductId, Qty) VALUES (s.ProductId, s.Qty)8WHEN NOT MATCHED BY SOURCE THEN9    DELETE10OUTPUT $action AS Action,11       COALESCE(inserted.ProductId, deleted.ProductId) AS ProductId,12       deleted.Qty AS OldQty, inserted.Qty AS NewQty;13 14SELECT * FROM dbo.BranchStock ORDER BY ProductId;

Output

Action  ProductId  OldQty  NewQty
------  ---------  ------  ------
INSERT          5    NULL      20
UPDATE          1      40      38
DELETE          9       5    NULL
UPDATE         12       0       6

ProductId  Qty
---------  ---
        1   38
        3   65
        5   20
       12    6

Product 9 was on the branch’s list but not counted, so it was deleted. For a DELETE, inserted. is all NULL, which is why ProductId comes from whichever side has it.

Trap 1: an unscoped NOT MATCHED BY SOURCE

“In the target but not in the source” means every target row the source does not mention. Sync a feed that covers one category into the whole Products table and every other product is a candidate for deletion. Scope the target with a CTE, so MERGE only sees the rows the source is about:

Query.sql

1WITH Building AS (2    SELECT * FROM dbo.Products WHERE CategoryId = 23)4MERGE Building AS t5USING (SELECT Name, Price FROM dbo.SupplierFeed WHERE CategoryId = 2) AS s6   ON t.Name = s.Name7WHEN MATCHED AND t.Price <> s.Price THEN8    UPDATE SET t.Price = s.Price9WHEN NOT MATCHED BY SOURCE THEN10    UPDATE SET t.Stock = 0          -- not in the feed: mark as unavailable11OUTPUT $action AS Action, inserted.Name, inserted.Price, inserted.Stock;

Output

Action  Name        Price  Stock
------  ----------  -----  -----
UPDATE  Sand 25 kg   4.30    220

Both Building products are in the feed, so the NOT MATCHED BY SOURCE clause found nothing to do. Without the CTE it would have zeroed the stock of all ten other products.

Trap 2: duplicate source rows

Each target row may be changed only once by a MERGE. If two source rows match the same target row, SQL Server cannot know which one wins, and stops:

Query.sql

1CREATE TABLE #Prices (Name nvarchar(60), Price decimal(10,2));2INSERT INTO #Prices VALUES (N'Claw hammer', 19.50), (N'Claw hammer', 19.95);3 4MERGE dbo.Products AS t5USING #Prices AS s ON t.Name = s.Name6WHEN MATCHED THEN UPDATE SET t.Price = s.Price;

Output

Msg 8672, Level 16, State 1, Line 4
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Fix the source first: decide which row wins (the latest, the highest) with ROW_NUMBER or GROUP BY in a CTE, and MERGE from that.

Trap 3: two sessions at once

MERGE is one statement, but it is not safe against a race by default. Two sessions can both find “not matched” for the same new product and both insert it, giving a duplicate or a key violation. For an upsert that runs concurrently, add a lock hint to the target:

Query.sql

1MERGE dbo.Products WITH (HOLDLOCK) AS t2USING (VALUES (N'Garden spade', 1, 24.00, 15, N'each'))3      AS s (Name, CategoryId, Price, Stock, Unit)4   ON t.Name = s.Name5WHEN MATCHED THEN6    UPDATE SET t.Price = s.Price, t.Stock = s.Stock7WHEN NOT MATCHED THEN8    INSERT (Name, CategoryId, Price, Stock, Unit, AddedOn)9    VALUES (s.Name, s.CategoryId, s.Price, s.Stock, s.Unit, '2024-07-01')10OUTPUT $action AS Action, inserted.ProductId, inserted.Name;

Output

Action  ProductId  Name
------  ---------  ------------
INSERT         13  Garden spade

HOLDLOCK (the same as SERIALIZABLE for this table) keeps a lock on the key range it checked until the statement ends, so a second session waits instead of inserting the same name. The same applies to the two-statement upsert: use WITH (UPDLOCK, HOLDLOCK) on the existence check inside the transaction. Locking is covered in the transactions module.

Quick check

In a MERGE, a row exists in the target but not in the source. Which clause handles it?

Quick check

In the OUTPUT of a MERGE, what is in deleted.Qty for a row that was inserted?

Practice

Upsert a single product with MERGE: set the Cordless drill’s Price to 84.00 and its Stock to 20 if it exists, or insert it in category 1 with Unit 'each' if not. Return the action, ProductId, old price and new price.

Show a hint

USING (VALUES (N'Cordless drill', 84.00, 20)) AS s (Name, Price, Stock), ON t.Name = s.Name, then WHEN MATCHED … WHEN NOT MATCHED … OUTPUT $action, …

Show the solution

Solution.sql

1MERGE dbo.Products AS t2USING (VALUES (N'Cordless drill', 84.00, 20)) AS s (Name, Price, Stock)3   ON t.Name = s.Name4WHEN MATCHED THEN5    UPDATE SET t.Price = s.Price, t.Stock = s.Stock6WHEN NOT MATCHED THEN7    INSERT (Name, CategoryId, Price, Stock, Unit, AddedOn)8    VALUES (s.Name, 1, s.Price, s.Stock, N'each', '2024-07-01')9OUTPUT $action AS Action, inserted.ProductId,10       deleted.Price AS OldPrice, inserted.Price AS NewPrice;

Output

Action  ProductId  OldPrice  NewPrice
------  ---------  --------  --------
UPDATE          2     89.00     84.00

Remember

An upsert updates the rows that exist and inserts the rest: UPDATE … JOIN, then INSERT … WHERE NOT EXISTS, in a transaction.
MERGE does it in one statement: target, USING source, ON condition, and WHEN clauses.
WHEN MATCHED (update/delete), NOT MATCHED BY TARGET (insert), NOT MATCHED BY SOURCE (update/delete).
OUTPUT $action, deleted.* and inserted.* show what happened to each row; OUTPUT INTO saves it.
MERGE must end with a semicolon.
Scope NOT MATCHED BY SOURCE with a CTE on the target, or it touches every row the source omits.
A target row matched by two source rows is an error: deduplicate the source first.
Concurrent upserts need WITH (HOLDLOCK), or UPDLOCK, HOLDLOCK on the two-statement version.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

MERGE, upserts and the OUTPUT clause

The upsert in two statements

MERGE: one statement

Syncing the branch stock

OUTPUT INTO: keep a record

A full sync: NOT MATCHED BY SOURCE

Trap 1: an unscoped NOT MATCHED BY SOURCE

Trap 2: duplicate source rows

Trap 3: two sessions at once

Remember