MERGE and Upserts: Insert, Update, Delete in One Step – SQL Server | Extra 2
Introduction
Real systems receive lists: a fee list from the accounts office, a book catalog from a supplier, a student list from another school. Some rows are new, some have changed, and some have disappeared.
Applying such a list is called an upsert: update the rows that exist, insert the
ones that do not. SQL Server's MERGE statement does this, and can also delete, in one step.
By the end of this lesson, you will be able to:
- Load a file into a staging table and compare it with a real table.
- Write a
MERGEwith insert, update, and delete branches. - See exactly what changed with
OUTPUT $action. - Avoid the three classic
MERGEtraps. - Write the same upsert without
MERGE.
Why this matters
The accounts office sends a revised fee list for Classes 9 and 10. Tuition goes up, transport fees are new, and Class 9 no longer pays a library fee. Typing these changes by hand into 30 rows is slow, and one missed row means wrong invoices for hundreds of families.
A careless MERGE is worse than typing. In this lesson, one small mistake tries to delete
the fees of eight other classes. Only a foreign key stops it.
The concept
Example: the shape of MERGE
MERGE compares a target (the real table) with a source (the new list), row by
row, using an ON condition. Each pair then falls into one of three branches:
| Branch | Meaning | Typical action |
|---|---|---|
WHEN MATCHED | The row is in both | UPDATE (or DELETE) |
WHEN NOT MATCHED BY TARGET | Only in the new list | INSERT |
WHEN NOT MATCHED BY SOURCE | Only in the table | DELETE (or UPDATE) |
Every branch is optional, and each can have an extra condition, such as
WHEN MATCHED AND t.Amount <> s.Amount. A MERGE must end with a semicolon.
Think of checking a class register against the office's new list. Names on both lists get their details corrected.
New names are added. Names only on your register are crossed out.
Example: the staging table
The source is usually a staging table: a table that holds the incoming file before it touches real data. Here it is a temp table from Extra 1, with a primary key so the file cannot contain the same fee twice:
CREATE TABLE #FeeUpload (
ClassId INT NOT NULL,
FeeType VARCHAR(10) NOT NULL,
Amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (ClassId, FeeType)
);
Example: getting the file into staging
The office usually sends a spreadsheet saved as CSV. There are three common ways to get it into a staging table:
| Way | Who uses it | Notes |
|---|---|---|
| SSMS "Import Flat File" wizard | A developer, once | Right-click the database, Tasks, Import Flat File |
BULK INSERT | A scheduled script | The file must sit where the SQL Server service can read it |
| The application | Office staff, every term | The Web API receives the uploaded file and inserts the rows into staging |
Whichever way you use, the merge itself stays the same. That is the point of staging: loading and applying are separate steps, and you can check the data in between.
Apply it to the SMS
The script loads the office's list for Classes 9 and 10, then runs five demos. Each one runs inside a transaction that is rolled back, so you can run the script again and again.
Show extras/e2-merge.sql
-- SQL Extra 2: MERGE and upserts.
-- The accounts office sends a revised fee list for Classes 9 and 10 (2026-27).
-- Every demo runs inside a transaction that is rolled back, so SchoolDb is unchanged.
USE SchoolDb;
GO
SET NOCOUNT ON;
GO
-- 0. The office's file, loaded into a staging table.
DROP TABLE IF EXISTS #FeeUpload;
CREATE TABLE #FeeUpload (
ClassId INT NOT NULL,
FeeType VARCHAR(10) NOT NULL,
Amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (ClassId, FeeType)
);
INSERT INTO #FeeUpload (ClassId, FeeType, Amount)
VALUES
(9, 'Tuition', 44100), -- was 42000
(9, 'Exam', 2000), -- unchanged
(9, 'Transport', 9000), -- new; Class 9 Library is no longer in the list
(10, 'Tuition', 44100), -- was 42000
(10, 'Library', 1500), -- unchanged
(10, 'Exam', 2500), -- was 2000
(10, 'Transport', 9000); -- new
GO
-- 1. Before any DELETE branch: preview what it would remove.
SELECT
(SELECT COUNT(*)
FROM dbo.FeeStructures AS t
WHERE t.AcademicYearId = 2
AND NOT EXISTS (SELECT 1 FROM #FeeUpload AS s
WHERE s.ClassId = t.ClassId AND s.FeeType = t.FeeType)) AS UnscopedDeletes,
(SELECT COUNT(*)
FROM dbo.FeeStructures AS t
WHERE t.AcademicYearId = 2
AND t.ClassId IN (SELECT ClassId FROM #FeeUpload)
AND NOT EXISTS (SELECT 1 FROM #FeeUpload AS s
WHERE s.ClassId = t.ClassId AND s.FeeType = t.FeeType)) AS ScopedDeletes;
GO
-- 2. The upsert: one MERGE inserts, updates and deletes. Run twice: the second run changes nothing.
BEGIN TRANSACTION;
WITH TargetFees AS (
SELECT *
FROM dbo.FeeStructures
WHERE AcademicYearId = 2
AND ClassId IN (SELECT ClassId FROM #FeeUpload) -- only the classes in the file
)
MERGE TargetFees WITH (HOLDLOCK) AS t
USING #FeeUpload AS s
ON t.ClassId = s.ClassId
AND t.FeeType = s.FeeType
WHEN MATCHED AND t.Amount <> s.Amount THEN
UPDATE SET t.Amount = s.Amount, t.UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
INSERT (AcademicYearId, ClassId, FeeType, Amount)
VALUES (2, s.ClassId, s.FeeType, s.Amount)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action AS MergeAction,
COALESCE(inserted.ClassId, deleted.ClassId) AS ClassId,
COALESCE(inserted.FeeType, deleted.FeeType) AS FeeType,
deleted.Amount AS OldAmount,
inserted.Amount AS NewAmount;
SELECT @@ROWCOUNT AS RowsChangedFirstRun;
WITH TargetFees AS (
SELECT *
FROM dbo.FeeStructures
WHERE AcademicYearId = 2
AND ClassId IN (SELECT ClassId FROM #FeeUpload)
)
MERGE TargetFees WITH (HOLDLOCK) AS t
USING #FeeUpload AS s
ON t.ClassId = s.ClassId
AND t.FeeType = s.FeeType
WHEN MATCHED AND t.Amount <> s.Amount THEN
UPDATE SET t.Amount = s.Amount, t.UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
INSERT (AcademicYearId, ClassId, FeeType, Amount)
VALUES (2, s.ClassId, s.FeeType, s.Amount)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SELECT @@ROWCOUNT AS RowsChangedSecondRun;
ROLLBACK TRANSACTION;
GO
-- 3. A duplicate row in the file: MERGE refuses to update one row twice (error 8672).
-- The copy has no primary key, so it accepts a second Class 9 tuition line.
DROP TABLE IF EXISTS #FeeUploadWithDuplicate;
SELECT ClassId, FeeType, Amount INTO #FeeUploadWithDuplicate FROM #FeeUpload;
INSERT INTO #FeeUploadWithDuplicate (ClassId, FeeType, Amount) VALUES (9, 'Tuition', 45000);
BEGIN TRY
BEGIN TRANSACTION;
MERGE dbo.FeeStructures AS t
USING #FeeUploadWithDuplicate AS s
ON t.AcademicYearId = 2
AND t.ClassId = s.ClassId
AND t.FeeType = s.FeeType
WHEN MATCHED AND t.Amount <> s.Amount THEN
UPDATE SET t.Amount = s.Amount;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT ERROR_NUMBER() AS ErrorNumber, LEFT(ERROR_MESSAGE(), 90) AS ErrorMessage;
END CATCH;
GO
-- 4. A DELETE branch without scoping tries to remove every other class's fees.
-- This time the foreign key from invoice items stops it (error 547).
BEGIN TRY
BEGIN TRANSACTION;
MERGE dbo.FeeStructures AS t
USING #FeeUpload AS s
ON t.ClassId = s.ClassId
AND t.FeeType = s.FeeType
WHEN NOT MATCHED BY SOURCE AND t.AcademicYearId = 2 THEN
DELETE;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT ERROR_NUMBER() AS ErrorNumber, LEFT(ERROR_MESSAGE(), 90) AS ErrorMessage;
END CATCH;
SELECT COUNT(*) AS FeeStructuresStillThere FROM dbo.FeeStructures;
GO
-- 5. The same upsert without MERGE: UPDATE, then INSERT, in one transaction.
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE t
SET t.Amount = s.Amount,
t.UpdatedAt = SYSUTCDATETIME()
FROM dbo.FeeStructures AS t WITH (UPDLOCK, HOLDLOCK)
JOIN #FeeUpload AS s
ON s.ClassId = t.ClassId
AND s.FeeType = t.FeeType
WHERE t.AcademicYearId = 2
AND t.Amount <> s.Amount;
SELECT @@ROWCOUNT AS RowsUpdated;
INSERT INTO dbo.FeeStructures (AcademicYearId, ClassId, FeeType, Amount)
SELECT 2, s.ClassId, s.FeeType, s.Amount
FROM #FeeUpload AS s
WHERE NOT EXISTS (
SELECT 1
FROM dbo.FeeStructures AS t WITH (UPDLOCK, HOLDLOCK)
WHERE t.AcademicYearId = 2
AND t.ClassId = s.ClassId
AND t.FeeType = s.FeeType
);
SELECT @@ROWCOUNT AS RowsInserted;
ROLLBACK TRANSACTION;
SET XACT_ABORT OFF;
GO
Example: preview before you delete
Before writing a delete branch, count what it would remove. Part 1 counts fee rows that are not in the file, first for the whole year, then only for the classes in the file:
| UnscopedDeletes | ScopedDeletes |
|---|---|
| 25 | 1 |
The file covers two classes, so the other eight classes are "missing" from it. A delete branch that looks at the whole table would remove 24 of their fees, plus Class 9 Library. Only the scoped count, 1, is what the office meant.
Example: the upsert and OUTPUT $action
Part 2 limits the target to the classes in the file with a CTE, then merges. OUTPUT
returns one row per change. $action says which branch made it:
| MergeAction | ClassId | FeeType | OldAmount | NewAmount |
|---|---|---|---|---|
| INSERT | 9 | Transport | NULL | 9000.00 |
| INSERT | 10 | Transport | NULL | 9000.00 |
| UPDATE | 9 | Tuition | 42000.00 | 44100.00 |
| DELETE | 9 | Library | 1500.00 | NULL |
| UPDATE | 10 | Tuition | 42000.00 | 44100.00 |
| UPDATE | 10 | Exam | 2000.00 | 2500.00 |
deleted holds the old values and inserted the new ones, as in the triggers of Lesson 23.
Class 9 Exam and Class 10 Library are not listed: their amounts did not change, so the
AND t.Amount <> s.Amount condition skipped them.
Example: run it twice, change nothing
The same MERGE runs again on the same file:
| RowsChangedFirstRun | RowsChangedSecondRun |
|---|---|
| 6 | 0 |
This is called idempotent: running it twice has the same effect as running it once. Imports get repeated by accident all the time. A good upsert makes that harmless.
Example: trap 1, a duplicate in the file
Part 3 adds a second Class 9 tuition line with a different amount. Which one should win? SQL Server refuses to guess:
Msg 8672: The MERGE statement attempted to UPDATE or DELETE the same row more than once.
The fix is a primary key on the staging table, or removing duplicates before the merge. The error is a good thing: it stops a half-understood file before it reaches the invoices. Ask the office which amount is right, and load the corrected file.
Example: trap 2, an unscoped DELETE branch
Part 4 uses the whole FeeStructures table as the target with a delete branch. It tries to
delete every fee of Classes 1 to 8:
Msg 547: The MERGE statement conflicted with the REFERENCE constraint "FK_FeeInvoiceItems_FeeStructures".
Some of those fees are already on invoices, so the foreign key blocked the whole statement.
FeeStructuresStillThere is still 30. A table without a foreign key would have lost
24 rows, silently.
Example: trap 3, two imports at once
If two sessions run the same MERGE at the same moment, both can decide a row is missing,
and both try to insert it. One then fails with a duplicate key error.
WITH (HOLDLOCK) on the target keeps the locks for the whole statement, so the second
session waits. Always add it to a MERGE that inserts.
Example: the upsert without MERGE
Many teams avoid MERGE because of these traps. Part 5 does the same work with two plain
statements in one transaction:
| RowsUpdated | RowsInserted |
|---|---|
| 3 | 2 |
UPDLOCK, HOLDLOCK plays the same role as HOLDLOCK above. This pattern is longer, but
every step is simple, and each step's row count is easy to check.
Example: which one should you choose?
| Question | MERGE | UPDATE + INSERT |
|---|---|---|
| Amount of code | Less | More |
| Deleting missing rows | One extra branch | A third statement |
| Traps to remember | Duplicates, scoping, concurrency | Concurrency only |
| Easy for a reviewer to check | Harder | Easier |
| Row counts | One total, or detail from OUTPUT | One per statement |
Both are correct when written carefully. Follow your team's standard, and whichever you choose, keep the transaction, the locking hints, and the preview.
Example: comparing values that can be NULL
The condition t.Amount <> s.Amount works because Amount is never NULL. For a nullable
column, such as a book's Publisher, it silently skips real changes:
DECLARE @Old NVARCHAR(100) = NULL, @New NVARCHAR(100) = N'Penguin';
SELECT
CASE WHEN @Old <> @New THEN 'Changed' ELSE 'Skipped' END AS WithNotEqual,
CASE WHEN @Old IS DISTINCT FROM @New THEN 'Changed' ELSE 'Skipped' END AS WithIsDistinctFrom;
| WithNotEqual | WithIsDistinctFrom |
|---|---|
| Skipped | Changed |
Any comparison with NULL is "unknown", not true (Lesson 16). IS DISTINCT FROM, new in
SQL Server 2022, treats two NULLs as equal and NULL versus a value as different.
Common mistakes
- A delete branch on the whole table. Every row missing from the file is deleted. Fix: limit the target with a CTE, and preview the count first.
- No key on the staging table. Duplicates cause error 8672 or the wrong amount. Fix: add a primary key, or remove duplicates first.
- Updating rows that did not change. Audit columns change and triggers fire for
nothing. Fix:
WHEN MATCHED AND t.Amount <> s.Amount. - Forgetting
NULLin that condition.<>is never true when one side is NULL. Fix: compare nullable columns withIS DISTINCT FROM(SQL Server 2022) or handle NULL. - No
HOLDLOCK. Two imports at once collide. Fix:MERGE target WITH (HOLDLOCK).
In real projects
The worst data loss I have seen in a school system was a MERGE with a delete branch. A
teacher uploaded the student list for one section, and the job removed the students of
every other section.
There was no foreign key on that table, so nothing stopped it. We restored from the backup, and lost a morning of attendance.
Now every import I design loads into staging first. It shows a preview, such as "6 changes, 1 delete", and a person confirms before the real merge runs.
Best practices
- Load files into a staging table with a primary key; never merge straight from raw input.
- Limit the target to the rows the file is responsible for.
- Preview inserts, updates, and deletes before running a merge with a delete branch.
- Use
HOLDLOCK, a transaction, andOUTPUTto log every change. - If your team finds
MERGEhard to review, useUPDATEplusINSERTinstead.
Recap
- An upsert updates existing rows and inserts new ones.
MERGEcompares a target with a source and has three branches.OUTPUT $actionshows every change; a second run changing 0 rows proves it is idempotent.- Error 8672 means the source matched one target row more than once.
- An unscoped delete branch deletes every row missing from the file.
UPDATEplusINSERTin a transaction is a simple, safe alternative.
Homework
Library module task: A supplier sends a catalog of books with ISBN, title, author,
and category. Merge it into Books, matching on ISBN.
Insert new books, update changed details, and never delete. Show every change with OUTPUT.
A correct result looks like: two changes: an INSERT for the new book, and an
UPDATE of Malgudi Days from Fiction to Short Stories. Wings of Fire is unchanged.
Show the solution
-- Library homework, Extra 2: sync a supplier's catalog into Books, matching on ISBN.
-- New books are inserted, changed details are updated, nothing is deleted.
-- The transaction is rolled back so you can run it again; change ROLLBACK to COMMIT to keep it.
USE SchoolDb;
GO
SET NOCOUNT ON;
GO
DROP TABLE IF EXISTS #Catalog;
CREATE TABLE #Catalog (
Isbn VARCHAR(13) NOT NULL PRIMARY KEY,
Title NVARCHAR(200) NOT NULL,
Author NVARCHAR(100) NOT NULL,
Category NVARCHAR(50) NOT NULL
);
INSERT INTO #Catalog (Isbn, Title, Author, Category)
VALUES
('9788173711466', N'Wings of Fire', N'A. P. J. Abdul Kalam', N'Biography'), -- unchanged
('9780143039655', N'Malgudi Days', N'R. K. Narayan', N'Short Stories'), -- new category
('9780000000017', N'Our School Garden', N'Practice Author', N'Science'); -- new (sample ISBN)
BEGIN TRANSACTION;
MERGE dbo.Books WITH (HOLDLOCK) AS t
USING #Catalog AS s
ON t.Isbn = s.Isbn
WHEN MATCHED AND (t.Title <> s.Title OR t.Author <> s.Author OR t.Category <> s.Category) THEN
UPDATE SET t.Title = s.Title,
t.Author = s.Author,
t.Category = s.Category,
t.UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
INSERT (Isbn, Title, Author, Category)
VALUES (s.Isbn, s.Title, s.Author, s.Category)
OUTPUT $action AS MergeAction, inserted.Isbn, inserted.Title, deleted.Category AS OldCategory, inserted.Category AS NewCategory;
ROLLBACK TRANSACTION;
GO
There is no delete branch: books that are missing from one supplier's catalog are still on the library's shelves.
Quiz
Which MERGE branch handles rows that are in the new list but not in the table?
Show answer
Answer: B. WHEN NOT MATCHED BY TARGET Not in the target yet, so it is usually an INSERT.
What does $action return in an OUTPUT clause?
Show answer
Answer: B. INSERT, UPDATE, or DELETE for each changed row It names the branch that changed each row.
What causes error 8672?
Show answer
Answer: B. A target row matched more than one source row SQL Server will not update or delete one row twice in one MERGE.
A MERGE on the whole FeeStructures table has WHEN NOT MATCHED BY SOURCE THEN DELETE, and the file covers 2 classes. What happens?
Show answer
Answer: B. It tries to delete the fees of every other class Every row missing from the file counts as not matched by source.
Running the same import twice changes 0 rows the second time. What is this property called?
Show answer
Answer: B. Idempotent Repeating it has the same effect as running it once.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.
Fresher (0-2 yrs)
F What is an upsert? concept
Updating a row if it already exists and inserting it if it does not, in one operation. Loading a revised fee list, where some fees change and some are new, is an upsert.
F What are the three branches of a MERGE statement? concept
WHEN MATCHED (in both, usually update), WHEN NOT MATCHED BY TARGET (only in the source,
usually insert), and WHEN NOT MATCHED BY SOURCE (only in the target, usually delete). Each
is optional, and the statement must end with a semicolon.
F What is a staging table? concept
A table that holds incoming data, such as an uploaded file, before it touches the real tables. You can check and clean the data there, then apply it with a merge.
Mid (3-5 yrs)
M How do you see what a MERGE changed? coding
Add an OUTPUT clause. $action returns INSERT, UPDATE, or DELETE for each row, deleted
holds the old values, and inserted the new ones. You can also send the output into a log
table with OUTPUT ... INTO.
M What causes error 8672 in a MERGE, and how do you prevent it? concept
One target row matched more than one source row, so SQL Server would update or delete it twice. Prevent it with a primary key on the staging table, or remove duplicates before the merge.
M Why add WITH (HOLDLOCK) to a MERGE? concept
Without it, two sessions running the same merge can both decide a row is missing and both
insert it, causing a duplicate key error. HOLDLOCK keeps the range locked for the whole
statement, so the second session waits.
Senior (6-9 yrs)
S A teacher uploaded one section's student list and the import deleted every other section's students. What went wrong, and how do you prevent it? scenario
The merge had a WHEN NOT MATCHED BY SOURCE THEN DELETE branch on the whole table, so every
row missing from the file counted as deleted. Limit the target to the rows the file covers,
preview deletes before running, and require confirmation. Foreign keys and backups are the last
line of defense.
How to answer: Explain the mechanism, then the immediate recovery (restore or rebuild from backup), then prevention at several layers: scoped targets, previews, confirmation, foreign keys, and tests with a partial file.
S MERGE or UPDATE plus INSERT: which do you choose for an upsert? concept
Both work when written carefully. MERGE is shorter and can also delete, but has more traps:
duplicates, scoping, and concurrency. UPDATE plus INSERT in a transaction with
UPDLOCK, HOLDLOCK is longer but easier to review. Follow the team standard.
How to answer: Compare on correctness, readability, and concurrency, and show you know the traps of each. Mention making the upsert idempotent, so a repeated import changes nothing.
Architect / Lead (10+ yrs)
A Design a safe fee-list import for 1,000 schools. design
Upload into a staging table with keys and validation, show a preview of inserts, updates, and
deletes, and require a person to confirm. Apply in a short transaction with locking hints,
log every change with OUTPUT, and keep the import idempotent so retries are harmless.
How to answer: Cover the full flow: upload, validate, preview, approve, apply, audit. Address scale (one school's import must never touch another school's rows) and recovery (undo from the change log).
Resources
- Code for this lesson: git tag
extra-2-merge(code repository coming soon) - Files:
extras/e2-merge.sql,library-assignment/e2-library-merge.sql - Video: coming soon
What's next
You can now load lists into tables. Next: sending data out as JSON, the format web and mobile apps speak, and reading JSON back in. Extra 3: JSON in SQL Server
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.