Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a MERGE Statement That Inserts Duplicate Rows Instead of Updating Them

- 26.09.26 - ErcanOPAK

🗄️ MERGE Was Supposed to Update, and It Inserted a Duplicate Instead

A `MERGE` statement written to “update if it exists, insert if it doesn’t” can end up inserting duplicate rows for keys that genuinely already exist – not because `MERGE` itself is buggy, but because the join condition used to match source and target rows doesn’t uniquely identify a row (matching on a non-unique or nullable column), so a row that SHOULD match an existing target row fails to match cleanly and falls through to the INSERT branch instead.

🔎 The Problem

MERGE INTO Customers AS target
USING StagingCustomers AS source
ON target.Email = source.Email    -- Email allows NULLs and isn'"'"'t unique
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (Email, Name) VALUES (source.Email, source.Name);

-- A staging row with a NULL Email (or an Email that exists but with
-- different casing, if the column collation is case-sensitive) never
-- satisfies the ON condition against an existing target row - SQL Server
-- correctly falls through to WHEN NOT MATCHED and inserts a "new" row
-- that'"'"'s actually a duplicate of an existing customer.

✅ Fix: Match on a Genuinely Unique, Non-Nullable Key

  • Join `MERGE`’s `ON` clause on the table’s actual primary key or a guaranteed-unique business key – never on a column that permits NULLs or duplicates, since `MERGE` (like any join) simply won’t match rows the way you intend if the matching column itself doesn’t uniquely identify one.
  • For a source of truth that genuinely lacks a unique natural key, adding an explicit surrogate key to the staging table (populated during an earlier ETL step) before the `MERGE` runs is worth the extra step – it turns an ambiguous match into a guaranteed one.
  • Auditing `MERGE` statements specifically for NULL-safe matching is worth doing as a standing check – `target.Email = source.Email` is false (not true) when both sides are NULL, so two “matching” NULL rows still fall through to INSERT unless the condition explicitly accounts for that with something like `(target.Email = source.Email OR (target.Email IS NULL AND source.Email IS NULL))`.

⚠️ Why This Slips Past Initial Testing

  • A test dataset built with clean, always-present email addresses can run through `MERGE` correctly every single time – the duplicate-insert behavior only appears once real-world data includes the NULLs, casing differences, or trailing whitespace that a hand-crafted test dataset conveniently avoids.
  • This is also a well-known source of subtler `MERGE` bugs beyond duplicates – Microsoft’s own documentation notes several edge cases (including a documented issue with `MERGE` and triggers under certain conditions) worth being aware of before relying on it for anything business-critical without careful testing against realistic data.

MERGE matches rows exactly the way you told it to, not the way you meant – and a non-unique join condition means ‘exists already’ and ‘looks new’ can both be true for the same row.

— Database Engineer

Related posts:

SQL: Performance Tuning with Query Hints

SQL: Performance Tuning with Query Hints

SQL Server: Fix a Computed Column Index That Silently Stops Being Used After a Session Changes ANSI ...

SQL Server: Fix a Computed Column Index That Silently Stops Being Used After a Session Changes ANSI ...

SQL: Reading Execution Plans to Eliminate Table Scans

SQL: Reading Execution Plans to Eliminate Table Scans

Views: 13

Post navigation

ASP.NET Core: Fix a SignalR Connection That Drops the Moment You Scale to a Second Server
C#: Fix a DateTime That Silently Loses Its Time Zone the Moment You Serialize It

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com