How Does the MERGE Statement Work in SQL Server?
💡 What is the MERGE Statement?
The MERGE Statement in SQL Server is a single command that can insert, update, or delete rows in a target table based on how they compare with rows from a source table. Instead of writing multiple queries, MERGE lets you handle everything in one smart, unified step.
📘 Quick Intro
Normally, you’d write separate queries: one for INSERT, another for UPDATE, and maybe another for DELETE.
With MERGE, SQL Server combines these into a single operation, comparing the source and target on a key and deciding what to do with each row automatically.
This makes your code shorter, cleaner, and easier to maintain.
🧠 Analogy / Short Story
Imagine you’re syncing two contact lists on your phone. - If the same person exists in both lists but their number changed, you update it. - If a new person shows up in the new list, you add them. - If someone is missing from the new list but still exists in the old one, you delete them. That’s exactly what the MERGE statement does—automated smart syncing between datasets.
🔧 Technical Breakdown
- 🔁 Comparison: MERGE always compares a
targettable with asourcetable. - ✅
WHEN MATCHED: Rows exist in both → usually perform anUPDATE. - ➕
WHEN NOT MATCHED BY TARGET: Row is new in source → perform anINSERT. - ➖
WHEN NOT MATCHED BY SOURCE: Row exists only in target → perform aDELETE. - ⚙️ ON clause: Defines the condition (like matching IDs) that determines how rows are compared.
- 📌 OUTPUT clause: Lets you return details about what changed—handy for auditing or debugging.
🎯 Purpose & Real-World Use Cases
- ✅ Keeping a staging table in sync with a live production table.
- ✅ Handling upsert logic (insert if not found, update if found) in one statement.
- ✅ Automating ETL pipelines where incoming source data must align perfectly with destination tables.
💻 Real Code Example
MERGE INTO Customers AS Target
USING NewCustomers AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
UPDATE SET Target.Name = Source.Name, Target.Email = Source.Email
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, Name, Email)
VALUES (Source.CustomerID, Source.Name, Source.Email)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
❓ Interview Q&A
Q1: What is the purpose of the MERGE statement in SQL Server?
A: To combine INSERT, UPDATE, and DELETE logic into a single command based on a join condition.
Q2: What does "WHEN MATCHED" mean?
A: It indicates the action to take (like UPDATE) when a row exists in both source and target.
Q3: Is MERGE ANSI-standard SQL?
A: Yes, but implementations can vary between databases.
Q4: Can MERGE cause deadlocks?
A: Yes, if not properly managed, especially in concurrent environments.
Q5: What happens if there are multiple matches in MERGE?
A: SQL Server throws an error unless you handle it with additional filters.
📝 MCQs
Q1. What is the primary purpose of MERGE in SQL Server?
- To delete duplicate rows
- To drop foreign keys
- To combine insert, update, and delete
- To create temporary tables
Q2. What clause defines row matching in MERGE?
- WHERE
- JOIN
- IF
- ON
Q3. What happens when a row only exists in source?
- UPDATE
- DELETE
- ROLLBACK
- INSERT into target
Q4. What can you use to capture changes in MERGE?
- RETURN
- OUTPUT
- ECHO
Q5. Which condition applies when a row is not in the source?
- WHEN NOT MATCHED BY TARGET
- WHEN MATCHED
- WHEN NOT MATCHED BY SOURCE
- WHERE NOT EXISTS
💡 Bonus Insight
Although powerful, MERGE can have concurrency issues and may perform worse than separate statements in some cases. Test performance and wrap it in transactions for safety.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!