T-SQL MERGE: The Synchronization Statement That Needs Caution

What is MERGE?

MERGE is a single T-SQL statement that compares two tables and synchronizes them:

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

In a single statement, you can:

• WHEN MATCHED: Update existing rows
• WHEN NOT MATCHED BY TARGET: Insert new rows from source
• WHEN NOT MATCHED BY SOURCE: Delete rows in target not in source

It's elegant. It's concise. It should be perfect for ETL and data synchronization. And it would be — if it didn't have a reputation for silent data corruption.

Why MERGE is Appealing

Without MERGE, synchronizing two tables is verbose:

-- The old way (separate statements)
UPDATE target SET value = s.value
FROM source s
WHERE target.id = s.id;

INSERT INTO target (id, value)
SELECT id, value FROM source
WHERE id NOT IN (SELECT id FROM target);

DELETE FROM target
WHERE id NOT IN (SELECT id FROM source);

With MERGE, it's one statement. Cleaner. Allegedly atomic. In theory, perfect.

The Gotchas: Why MERGE Has a Bad Reputation

Gotcha 1: Multiple Matches

If your JOIN condition matches a row in target to multiple rows in source, MERGE executes the WHEN MATCHED clause multiple times for the same target row:

-- This causes issues
MERGE INTO Orders AS t
USING OrderHistory AS s
ON t.OrderID = s.OrderID  -- ← What if one OrderID has multiple entries in history?
WHEN MATCHED THEN
    UPDATE SET t.Amount = s.Amount;  -- Updates multiple times?

SQL Server will either error or produce unexpected results. There's no atomicity guarantee here.

Gotcha 2: OUTPUT Clause Unreliability

MERGE with OUTPUT can report incorrect row counts or duplicates:

MERGE INTO target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED BY TARGET THEN INSERT (id, value) VALUES (s.id, s.value)
OUTPUT inserted.id, deleted.id, $action;  -- ← OUTPUT can be wrong

The OUTPUT clause may report rows that weren't actually modified, or miss rows that were. This is a known SQL Server bug affecting specific versions.

Gotcha 3: NONCLUSTERED Index Hints Break MERGE

If the target table has a nonclustered index and you hint it, MERGE can behave unexpectedly:

MERGE INTO target WITH (INDEX(idx_target)) AS t  -- ← Can cause issues
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.value = s.value;

The hint can force inefficient execution plans or cause deadlocks.

Gotcha 4: Execution Order is Not Guaranteed

You might assume MERGE processes WHEN clauses in order (MATCHED first, then NOT MATCHED BY TARGET, then NOT MATCHED BY SOURCE). It doesn't:

-- You might assume:
-- 1. Updates matched rows
-- 2. Inserts new rows
-- 3. Deletes old rows

-- But SQL Server might execute in any order, or in parallel
-- This can cause constraint violations if not careful

If your tables have foreign keys or unique constraints, MERGE can violate them mid-execution.

Gotcha 5: Triggers Fire Per Row, Not Per Statement

If the target table has triggers, they fire for each row modified:

-- Target table has an AFTER UPDATE trigger
-- MERGE updates 1000 rows
-- Trigger fires 1000 times
-- If trigger is expensive, performance degrades dramatically

You might expect one trigger invocation per MERGE. You get one per affected row.

Gotcha 6: MERGE Doesn't Handle Distributed Transactions Well

Using MERGE across linked servers or in a distributed transaction can cause locking issues or deadlocks.

Safe MERGE Patterns

Pattern 1: Simple Upsert (No DELETE)

If you only INSERT or UPDATE (no DELETE), MERGE is safe:

MERGE INTO target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED AND t.value <> s.value THEN
    UPDATE SET t.value = s.value, t.updated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, value, created)
    VALUES (s.id, s.value, GETDATE());
-- Add a condition to avoid updating if no change

This is relatively safe as long as:

• JOIN condition is unique (no multiple matches)
• You don't use DELETE
• You add conditions to avoid unnecessary updates

Pattern 2: Validate Before Merge

Ensure no duplicate keys in source:

-- Validate source has no duplicates
IF EXISTS (
    SELECT id, COUNT(*)
    FROM source
    GROUP BY id
    HAVING COUNT(*) > 1
)
BEGIN
    RAISERROR('Source has duplicate keys', 16, 1);
    RETURN;
END;

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

Pattern 3: Separate Statements (Safer Alternative)

If synchronization is critical, use separate UPDATE/INSERT/DELETE statements:

BEGIN TRANSACTION;

-- Update matched rows
UPDATE target
SET value = s.value
FROM source s
WHERE target.id = s.id;

-- Insert new rows
INSERT INTO target (id, value)
SELECT s.id, s.value
FROM source s
WHERE NOT EXISTS (SELECT 1 FROM target WHERE id = s.id);

-- Delete rows not in source
DELETE FROM target
WHERE NOT EXISTS (SELECT 1 FROM source WHERE source.id = target.id);

COMMIT TRANSACTION;

Longer, but each statement is predictable and can be debugged independently.

When to Use MERGE vs. Alternatives

Scenario	MERGE?	Alternative
Simple upsert (insert/update)	✓ Safe if no duplicates	Separate UPDATE + INSERT
Complex sync (insert/update/delete)	⚠ Risky	Separate statements
ETL with large volumes	⚠ Test first	Separate statements (better control)
Real-time sync with triggers	✗ No (triggers fire per row)	Separate statements + one trigger call
Distributed transactions	✗ No (locking issues)	Separate statements
Cross-server sync (linked servers)	✗ No (complex locking)	INSERT/UPDATE via linked server

Real-World Example: The Data Duplication Bug

A company used MERGE to sync their customer master data nightly:

MERGE INTO Customers t
USING CustomerStaging s
ON t.CustomerID = s.CustomerID
WHEN MATCHED THEN UPDATE SET t.Name = s.Name, t.Email = s.Email
WHEN NOT MATCHED BY TARGET THEN INSERT (...) VALUES (...)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

One night, the staging table accidentally had duplicate customer IDs (data quality issue). MERGE encountered multiple matches for the same target customer. The behavior was undefined — some customers got updated multiple times, others were deleted and reinserted.

Result: 50,000 customer records with corrupted email addresses. Customer emails bounced. Support got flooded. Investigation took 8 hours.

Root cause: MERGE was used without validating source data uniqueness.

Lesson: Always validate source data before MERGE. Use separate statements for critical synchronization.

Best Practices

1. Validate Source Data First

-- Before any MERGE, check:
-- No duplicate keys
-- No NULL values in join columns
-- All foreign keys are valid

2. Add Conditions to WHEN Clauses

WHEN MATCHED AND (t.value <> s.value OR t.value IS NULL)
    THEN UPDATE ...  -- Only update if actually different

3. Test with Real Data

Don't assume MERGE works until you've tested it with actual source/target data, including edge cases (duplicates, NULLs, constraints).

4. Use Separate Statements for Critical Data

For financial, medical, or audit-critical data, avoid MERGE. Use separate UPDATE/INSERT/DELETE statements in a transaction.

5. Monitor MERGE Performance

MERGE can be inefficient. Compare execution time against separate statements. Separate statements often run faster and use better execution plans.

6. Document Edge Cases

If you use MERGE, document:

• What happens if source has duplicates
• What happens if target has triggers
• Expected row counts before/after
• Any constraints that might be violated

The Verdict

MERGE is convenient for simple upserts but problematic for complex synchronization. When in doubt, use separate INSERT/UPDATE/DELETE statements. They're more predictable, easier to debug, and often faster.

MERGE has its place in simple, well-tested scenarios. But for production ETL and critical data sync, separate statements give you more control and fewer surprises.

If you do use MERGE, always validate source data, test thoroughly, and monitor performance. And document why you chose MERGE instead of safer alternatives.