SQL Server Interview Question #178

What is the MERGE statement?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

MERGE can combine insert, update, and delete-style synchronization logic between a source and target in one statement.

Although concise, MERGE has historically had important correctness, concurrency, and implementation caveats in SQL Server. Production teams often prefer explicit INSERT/UPDATE/DELETE statements inside a carefully designed transaction because their behavior can be easier to reason about and test.

If MERGE is used, its concurrency semantics, matching rules, duplicate source rows, and the SQL Server version's known behavior should be evaluated carefully.

Code Example

-- Simplified syntax example:
MERGE dbo.TargetProducts AS t
USING dbo.SourceProducts AS s
    ON t.Id = s.Id
WHEN MATCHED THEN
    UPDATE SET t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name) VALUES (s.Id, s.Name);