Skip to main content

MERGE and Upserts: Insert, Update, Delete in One Step – SQL Server | Extra 2

Published / updated

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 MERGE with insert, update, and delete branches.
  • See exactly what changed with OUTPUT $action.
  • Avoid the three classic MERGE traps.
  • 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:

BranchMeaningTypical action
WHEN MATCHEDThe row is in bothUPDATE (or DELETE)
WHEN NOT MATCHED BY TARGETOnly in the new listINSERT
WHEN NOT MATCHED BY SOURCEOnly in the tableDELETE (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:

Example
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:

WayWho uses itNotes
SSMS "Import Flat File" wizardA developer, onceRight-click the database, Tasks, Import Flat File
BULK INSERTA scheduled scriptThe file must sit where the SQL Server service can read it
The applicationOffice staff, every termThe 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
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:

UnscopedDeletesScopedDeletes
251

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:

MergeActionClassIdFeeTypeOldAmountNewAmount
INSERT9TransportNULL9000.00
INSERT10TransportNULL9000.00
UPDATE9Tuition42000.0044100.00
DELETE9Library1500.00NULL
UPDATE10Tuition42000.0044100.00
UPDATE10Exam2000.002500.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:

RowsChangedFirstRunRowsChangedSecondRun
60

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:

Output
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:

Output
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:

RowsUpdatedRowsInserted
32

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?​

QuestionMERGEUPDATE + INSERT
Amount of codeLessMore
Deleting missing rowsOne extra branchA third statement
Traps to rememberDuplicates, scoping, concurrencyConcurrency only
Easy for a reviewer to checkHarderEasier
Row countsOne total, or detail from OUTPUTOne 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:

Example
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;
WithNotEqualWithIsDistinctFrom
SkippedChanged

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​

  1. 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.
  2. No key on the staging table. Duplicates cause error 8672 or the wrong amount. Fix: add a primary key, or remove duplicates first.
  3. Updating rows that did not change. Audit columns change and triggers fire for nothing. Fix: WHEN MATCHED AND t.Amount <> s.Amount.
  4. Forgetting NULL in that condition. <> is never true when one side is NULL. Fix: compare nullable columns with IS DISTINCT FROM (SQL Server 2022) or handle NULL.
  5. No HOLDLOCK. Two imports at once collide. Fix: MERGE target WITH (HOLDLOCK).

In real projects​

From 16 years of .NET 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, and OUTPUT to log every change.
  • If your team finds MERGE hard to review, use UPDATE plus INSERT instead.

Recap​

  • An upsert updates existing rows and inserts new ones.
  • MERGE compares a target with a source and has three branches.
  • OUTPUT $action shows 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.
  • UPDATE plus INSERT in 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-assignment/e2-library-merge.sql
-- 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​

  1. Which MERGE branch handles rows that are in the new list but not in the table?

    1. WHEN MATCHED
    2. WHEN NOT MATCHED BY TARGET
    3. WHEN NOT MATCHED BY SOURCE
    4. OUTPUT
    Show answer

    Answer: B. WHEN NOT MATCHED BY TARGET Not in the target yet, so it is usually an INSERT.

  2. What does $action return in an OUTPUT clause?

    1. The row count
    2. INSERT, UPDATE, or DELETE for each changed row
    3. The error number
    4. The source file name
    Show answer

    Answer: B. INSERT, UPDATE, or DELETE for each changed row It names the branch that changed each row.

  3. What causes error 8672?

    1. A missing semicolon
    2. A target row matched more than one source row
    3. A foreign key
    4. HOLDLOCK
    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.

  4. A MERGE on the whole FeeStructures table has WHEN NOT MATCHED BY SOURCE THEN DELETE, and the file covers 2 classes. What happens?

    1. Only the 2 classes change
    2. It tries to delete the fees of every other class
    3. Nothing
    4. It inserts duplicates
    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.

  5. Running the same import twice changes 0 rows the second time. What is this property called?

    1. Atomic
    2. Idempotent
    3. Durable
    4. Isolated
    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.

See the example · Permalink

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.

See the example · Permalink

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.

See the example · Permalink

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.

See the example · Permalink

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.

See the example · Permalink

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.

See the example · Permalink

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.

See the example · Permalink

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.

See the example · Permalink

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).

See the example · Permalink

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.