SQL Topics
MERGE Statement
title: MERGE Statement
In the previous lesson, you learned about:
which allows SQL queries to perform conditional logic.
Now we move to one of the most powerful SQL statements used in enterprise systems:
MERGE StatementThe MERGE statement allows:
INSERT
UPDATE
DELETEoperations to be performed in a single SQL statement.
It is commonly used for:
Data Synchronization
ETL Processes
Data Warehousing
Data Migration
UPSERT OperationsMERGE is widely used in enterprise applications because it simplifies complex data management tasks.
What is MERGE Statement?
The MERGE statement combines data from a source table into a target table.
Based on matching conditions, SQL can:
Update Existing Rows
Insert New Rows
Delete Unmatched Rowsall within a single statement.
Simple Definition
MERGE is a SQL statement that synchronizes data between two tables using INSERT, UPDATE, and DELETE operations in one command.
Why is MERGE Needed?
Suppose we have:
Employee_Master
| EmployeeID | Name | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 60000 |
Employee_Staging
| EmployeeID | Name | Salary |
|---|---|---|
| 1 | Rahul | 55000 |
| 3 | Amit | 70000 |
Requirement:
Update Rahul's Salary
Insert AmitWithout MERGE:
UPDATE Query
+
INSERT Queryrequired separately.
MERGE handles both in one statement.
MERGE Workflow
Source Table
↓
Compare Rows
↓
Match?
↙ ↘
Yes No
↓ ↓
UPDATE INSERTOptional:
Not Matched By Source
↓
DELETEBasic MERGE Syntax
MERGE TargetTable AS T
USING SourceTable AS S
ON MatchingCondition
WHEN MATCHED THEN
UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...;Understanding the Syntax
MERGE
Starts the operation.
TargetTable
Destination table.
USING
Source table.
ON
Matching condition.
WHEN MATCHED
Rows exist in both tables.
Usually:
UPDATEWHEN NOT MATCHED
Rows exist only in source.
Usually:
INSERTSample Tables
Target Table:
CREATE TABLE Employee_Master
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary INT
);Source Table:
CREATE TABLE Employee_Staging
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary INT
);Sample Data
Master:
1 Rahul 50000
2 Priya 60000Staging:
1 Rahul 55000
3 Amit 70000MERGE Example
MERGE Employee_Master AS T
USING Employee_Staging AS S
ON T.EmployeeID =
S.EmployeeID
WHEN MATCHED THEN
UPDATE
SET T.Salary =
S.Salary
WHEN NOT MATCHED THEN
INSERT
(
EmployeeID,
EmployeeName,
Salary
)
VALUES
(
S.EmployeeID,
S.EmployeeName,
S.Salary
);Result
Before:
| ID | Name | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 60000 |
After:
| ID | Name | Salary |
|---|---|---|
| 1 | Rahul | 55000 |
| 2 | Priya | 60000 |
| 3 | Amit | 70000 |
MERGE with DELETE
Requirement:
Delete Employees
Not Present In SourceSyntax:
WHEN NOT MATCHED BY SOURCE THEN
DELETEExample
MERGE Employee_Master AS T
USING Employee_Staging AS S
ON T.EmployeeID =
S.EmployeeID
WHEN MATCHED THEN
UPDATE
SET T.Salary =
S.Salary
WHEN NOT MATCHED THEN
INSERT
(
EmployeeID,
EmployeeName,
Salary
)
VALUES
(
S.EmployeeID,
S.EmployeeName,
S.Salary
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;Result
Employees missing from source are removed.
MERGE Conditions
MERGE supports additional filtering.
Example:
WHEN MATCHED
AND T.Salary <> S.Salary
THEN
UPDATE ...Only updates changed records.
UPSERT Concept
MERGE is commonly called:
UPSERTMeaning:
UPDATE
+
INSERTWhy UPSERT is Important?
Without UPSERT:
IF EXISTS
UPDATE
ELSE
INSERTrequires multiple statements.
MERGE simplifies the process.
Real-World Example: Banking
Requirement:
Synchronize Customer RecordsSource:
Daily Import FileTarget:
Customer DatabaseMERGE updates existing customers and inserts new ones.
Real-World Example: E-Commerce
Requirement:
Product Catalog SynchronizationSource:
Supplier FeedTarget:
Product DatabaseMERGE updates prices and adds new products.
Real-World Example: Payroll
Requirement:
Update Employee Salary DataNew payroll data merged automatically.
Real-World Example: University
Requirement:
Import Student RecordsMERGE synchronizes admissions data.
MERGE vs INSERT
INSERT:
Adds New Rows OnlyMERGE:
Insert
Update
Deleteall possible.
MERGE vs UPDATE
UPDATE:
Modifies Existing RowsMERGE:
Can Insert Missing Rowstoo.
MERGE vs UPSERT Logic
Traditional Approach:
IF EXISTS
BEGIN
UPDATE
END
ELSE
BEGIN
INSERT
ENDMERGE Approach:
MERGE ...Single statement.
Data Warehouse Example
Very common use case.
Source:
Daily Sales FeedTarget:
Data WarehouseMERGE synchronizes records efficiently.
Slowly Changing Dimensions (SCD)
MERGE is heavily used in:
Data Warehousingfor:
Dimension Updatesespecially:
SCD Type 1and
SCD Type 2implementations.
Advantages of MERGE
Single Statement
Handles multiple operations.
Better Readability
Centralized synchronization logic.
Reduces Code Complexity
Fewer SQL statements.
Supports UPSERT
Major advantage.
Ideal for ETL Processes
Commonly used in data warehouses.
Disadvantages of MERGE
Complex Syntax
Harder for beginners.
Vendor Differences
Behavior varies between databases.
Performance Considerations
Large merges require optimization.
Debugging Challenges
Multiple operations occur together.
Common Mistakes
Incorrect Matching Condition
May update wrong rows.
Missing Primary Keys
Can create duplicates.
Unintended Deletes
WHEN NOT MATCHED BY SOURCE can remove data.
Large Unoptimized Merges
Can affect performance.
Best Practices
Use Proper Keys
Primary keys recommended.
Test on Small Data First
Validate behavior.
Add Conditions Carefully
Avoid unnecessary updates.
Backup Before Large MERGE Operations
Protect data.
Monitor Execution Plans
Optimize performance.
Common Interview Questions
What is MERGE Statement?
A statement that performs INSERT, UPDATE, and DELETE operations in a single command.
What is UPSERT?
An operation that updates existing rows or inserts new rows.
Why is MERGE used?
To synchronize data between tables.
Can MERGE perform DELETE operations?
Yes.
Using:
WHEN NOT MATCHED BY SOURCE
THEN DELETEWhere is MERGE commonly used?
ETL processes, data warehouses, synchronization systems, and enterprise applications.
Summary
The MERGE statement is a powerful SQL feature used to synchronize data between source and target tables. It combines INSERT, UPDATE, and DELETE operations into a single statement and is widely used in enterprise systems, ETL pipelines, and data warehouses.
In this lesson, you learned:
- What MERGE is
- MERGE Syntax
- UPDATE with MERGE
- INSERT with MERGE
- DELETE with MERGE
- UPSERT Concept
- Real-world examples
- Advantages and disadvantages
- Best practices
Mastering MERGE is essential because enterprise databases frequently rely on data synchronization and UPSERT operations.
Next Step
Continue to the next lesson:
Dynamic SQL →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for MERGE Statement.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, advanced, merge
Related SQL Topics