What is the SQL MERGE statement used for?

The MERGE statement combines insert, update, and delete operations into one query based on matching rows between a source and target table.

What does WHEN NOT MATCHED BY SOURCE do in MERGE?

It specifies a DELETE action for rows that exist in the target table but not in the source table.

How Does the MERGE Statement Work in SQL Server? | FullStackPrep.Dev

💡 Concept Name

MERGE Statement in SQL Server is a powerful command that allows conditional INSERT, UPDATE, or DELETE actions based on the comparison between source and target tables.

📘 Quick Intro

Instead of writing separate statements for insert, update, or delete, the MERGE statement unifies all these operations based on matching keys between two datasets.

🧠 Analogy / Short Story

Think of MERGE like synchronizing two contact lists. If a contact exists in both, update it. If it's only in the new list, add it. If it’s missing from the new list but present in the old, delete it. MERGE automates this smart syncing.

🔧 Technical Explanation

🔁 MERGE compares a target and a source table.
✅ WHEN MATCHED: Perform an UPDATE if rows match.
➕ WHEN NOT MATCHED BY TARGET: Perform an INSERT.
➖ WHEN NOT MATCHED BY SOURCE: Perform a DELETE.
⚠️ Must include an ON clause to define the matching condition.
📌 SQL Server supports outputting changes using the OUTPUT clause.

🎯 Purpose & Use Case

✅ Synchronize a staging table with a live production table.
✅ Simplify conditional upsert operations.
✅ Automate ETL processes where source and destination data must align.

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

PRINT
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!

📄 PDF Download Coming Soon

How Does the MERGE Statement Work in SQL Server?