The MERGE statement is SQL’s convenient tool for synchronizing data between two tables. It lets you perform INSERT, UPDATE, and DELETE operations in a single statement based on whether matching records exist. Instead of writing separate logic to check if a record exists and then deciding what to do with it, MERGE handles all of that in one go.
Most major database systems support MERGE, including SQL Server, Oracle, and DB2. PostgreSQL added native MERGE support in version 15, but if you’re on an older version, you can use INSERT … ON CONFLICT as an alternative. MySQL doesn’t have MERGE but offers INSERT … ON DUPLICATE KEY UPDATE for similar functionality.
Why Use MERGE?
The main appeal of MERGE is efficiency and simplicity. Instead of writing separate logic to check if a record exists and then deciding whether to insert or update it, MERGE handles all of that in one go. It’s particularly useful when you’re:
- Synchronizing staging tables with production tables
- Importing data from external sources
- Maintaining slowly changing dimensions in data warehouses
- Keeping lookup tables current
Basic Syntax
Here’s the general structure of a MERGE statement:
MERGE INTO target_table
USING source_table
ON matching_condition
WHEN MATCHED THEN
UPDATE SET column1 = value1, column2 = value2
WHEN NOT MATCHED THEN
INSERT (column1, column2) VALUES (value1, value2);The statement compares a source (which can be a table, view, or query result) against a target table using a join condition. Based on whether matches are found, it executes the appropriate action.
Example
Let’s say you’re managing inventory for a music equipment store. You receive daily updates about product stock levels and prices from your suppliers, and you need to keep your database current.
First, let’s set up our tables:
-- Create the main inventory table
CREATE TABLE Inventory (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
StockLevel INT,
UnitPrice DECIMAL(10, 2),
LastUpdated DATETIME
);
-- Create a staging table for incoming updates
CREATE TABLE InventoryUpdates (
ProductID INT,
ProductName VARCHAR(100),
StockLevel INT,
UnitPrice DECIMAL(10, 2)
);
-- Populate initial inventory
INSERT INTO Inventory VALUES
(101, 'Gibson Les Paul Standard', 5, 2499.99, '2025-01-15'),
(102, 'Fender Stratocaster', 8, 1299.99, '2025-01-15'),
(103, 'Roland TD-17 Drum Kit', 3, 1799.99, '2025-01-15'),
(104, 'Yamaha P-125 Piano', 6, 649.99, '2025-01-15');
-- Simulate incoming updates
INSERT INTO InventoryUpdates VALUES
(101, 'Gibson Les Paul Standard', 7, 2499.99), -- Stock increased
(102, 'Fender Stratocaster', 8, 1199.99), -- Price reduced
(105, 'Boss Katana 50 Amp', 12, 229.99); -- New product
SELECT * FROM Inventory;
SELECT * FROM InventoryUpdates;Output:
ProductID ProductName StockLevel UnitPrice LastUpdated
--------- ------------------------ ---------- --------- ------------------------
101 Gibson Les Paul Standard 5 2499.99 2025-01-15T00:00:00.000Z
102 Fender Stratocaster 8 1299.99 2025-01-15T00:00:00.000Z
103 Roland TD-17 Drum Kit 3 1799.99 2025-01-15T00:00:00.000Z
104 Yamaha P-125 Piano 6 649.99 2025-01-15T00:00:00.000Z
4 row(s) returned
ProductID ProductName StockLevel UnitPrice
--------- ------------------------ ---------- ---------
101 Gibson Les Paul Standard 7 2499.99
102 Fender Stratocaster 8 1199.99
105 Boss Katana 50 Amp 12 229.99
3 row(s) returned
Now you need to update existing products, insert new ones, and track when changes occurred. MERGE is ideal for this situation:
MERGE INTO Inventory AS target
USING InventoryUpdates AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
UPDATE SET
target.StockLevel = source.StockLevel,
target.UnitPrice = source.UnitPrice,
target.LastUpdated = GETDATE()
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, StockLevel, UnitPrice, LastUpdated)
VALUES (source.ProductID, source.ProductName, source.StockLevel,
source.UnitPrice, GETDATE());
SELECT * FROM Inventory;
SELECT * FROM InventoryUpdates;Result:
ProductID ProductName StockLevel UnitPrice LastUpdated
--------- ------------------------ ---------- --------- ------------------------
101 Gibson Les Paul Standard 7 2499.99 2025-11-19T06:55:44.623Z
102 Fender Stratocaster 8 1199.99 2025-11-19T06:55:44.623Z
103 Roland TD-17 Drum Kit 3 1799.99 2025-01-15T00:00:00.000Z
104 Yamaha P-125 Piano 6 649.99 2025-01-15T00:00:00.000Z
105 Boss Katana 50 Amp 12 229.99 2025-11-19T06:55:44.623Z
5 row(s) returned
ProductID ProductName StockLevel UnitPrice
--------- ------------------------ ---------- ---------
101 Gibson Les Paul Standard 7 2499.99
102 Fender Stratocaster 8 1199.99
105 Boss Katana 50 Amp 12 229.99
3 row(s) returned
After running this MERGE, the Inventory table reflects all the changes. Product 101 has increased stock, Product 102 has a new price, and Product 105 has been added. All in one statement.
The InventoryUpdates table remains the same.
Handling Deletions
MERGE can also remove records that exist in the target but not in the source. This is useful when you want your target table to exactly mirror the source. Let’s say products that aren’t in the update file should be marked as discontinued by removing them:
MERGE INTO Inventory AS target
USING InventoryUpdates AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
UPDATE SET
target.StockLevel = source.StockLevel,
target.UnitPrice = source.UnitPrice,
target.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, StockLevel, UnitPrice, LastUpdated)
VALUES (source.ProductID, source.ProductName, source.StockLevel,
source.UnitPrice, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SELECT * FROM Inventory;
SELECT * FROM InventoryUpdates;Result:
ProductID ProductName StockLevel UnitPrice LastUpdated
--------- ------------------------ ---------- --------- ------------------------
101 Gibson Les Paul Standard 7 2499.99 2025-11-19T06:56:56.693Z
102 Fender Stratocaster 8 1199.99 2025-11-19T06:56:56.693Z
105 Boss Katana 50 Amp 12 229.99 2025-11-19T06:56:56.693Z
3 row(s) returned
ProductID ProductName StockLevel UnitPrice
--------- ------------------------ ---------- ---------
101 Gibson Les Paul Standard 7 2499.99
102 Fender Stratocaster 8 1199.99
105 Boss Katana 50 Amp 12 229.99
3 row(s) returned
The WHEN NOT MATCHED BY SOURCE clause identifies records in the target that don’t have corresponding entries in the source. Be careful with this one because you could accidentally delete data you meant to keep.
Adding Conditions
You can add conditions to make your MERGE logic more sophisticated. Maybe you only want to update if the price has actually changed:
-- Clear and repopulate the staging table with price changes
DELETE FROM InventoryUpdates;
INSERT INTO InventoryUpdates VALUES
(101, 'Gibson Les Paul Standard', 7, 2499.99), -- Stock and price same (no update)
(102, 'Fender Stratocaster', 8, 1149.99), -- Price reduced from 1199.99
(105, 'Boss Katana 50 Amp', 12, 249.99); -- Price increased from 229.99
-- Now run the conditional MERGE
MERGE INTO Inventory AS target
USING InventoryUpdates AS source
ON target.ProductID = source.ProductID
WHEN MATCHED AND (target.StockLevel != source.StockLevel
OR target.UnitPrice != source.UnitPrice) THEN
UPDATE SET
target.StockLevel = source.StockLevel,
target.UnitPrice = source.UnitPrice,
target.LastUpdated = GETDATE()
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, StockLevel, UnitPrice, LastUpdated)
VALUES (source.ProductID, source.ProductName, source.StockLevel,
source.UnitPrice, GETDATE());
SELECT * FROM Inventory;
SELECT * FROM InventoryUpdates;Result:
ProductID ProductName StockLevel UnitPrice LastUpdated
--------- ------------------------ ---------- --------- ------------------------
101 Gibson Les Paul Standard 7 2499.99 2025-11-19T06:56:56.693Z
102 Fender Stratocaster 8 1149.99 2025-11-19T06:58:03.673Z
105 Boss Katana 50 Amp 12 249.99 2025-11-19T06:58:03.673Z
3 row(s) returned
ProductID ProductName StockLevel UnitPrice
--------- ------------------------ ---------- ---------
101 Gibson Les Paul Standard 7 2499.99
102 Fender Stratocaster 8 1149.99
105 Boss Katana 50 Amp 12 249.99
3 row(s) returned
This prevents unnecessary updates to rows when values haven’t changed, which can improve performance and avoid triggering downstream processes like audit logs. We can see that the first row’s timestamp hasn’t changed, which means it wasn’t updated (because its data hadn’t changed).
Things to Watch Out For
- Unique matching conditions: Your
ONclause should identify at most one row in the target for each source row. If multiple rows match, you’ll probably get an error. Use appropriate keys andWHEREclauses to ensure uniqueness. - Transaction handling:
MERGEis a single statement, but it can modify many rows. If something fails partway through, the entireMERGErolls back. This atomicity is usually what you want, but be aware of it when working with large datasets. - Permissions: You need
INSERT,UPDATE, andDELETEpermissions on the target table to execute a fullMERGEstatement. If you’re only doing certain operations, you only need those specific permissions. - Performance: For very large datasets,
MERGEisn’t always the fastest option. Sometimes separateINSERT,UPDATE, andDELETEstatements with proper indexing can outperform aMERGE. Test with your actual data volumes to see what works best.
The OUTPUT Clause
SQL Server’s OUTPUT clause works with MERGE to capture information about what happened during the operation. This can be useful for logging or debugging:
MERGE INTO Inventory AS target
USING InventoryUpdates AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
UPDATE SET
target.StockLevel = source.StockLevel,
target.UnitPrice = source.UnitPrice,
target.LastUpdated = GETDATE()
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, StockLevel, UnitPrice, LastUpdated)
VALUES (source.ProductID, source.ProductName, source.StockLevel,
source.UnitPrice, GETDATE())
OUTPUT $action,
inserted.ProductID,
inserted.ProductName,
deleted.StockLevel AS OldStock,
inserted.StockLevel AS NewStock;Result:
$action ProductID ProductName OldStock NewStock
------- --------- ------------------------ -------- --------
UPDATE 101 Gibson Les Paul Standard 7 7
UPDATE 102 Fender Stratocaster 8 8
UPDATE 105 Boss Katana 50 Amp 12 12
3 row(s) returned
The OUTPUT clause shows which action was performed (INSERT or UPDATE) and gives you access to both the old values (via deleted) and new values (via inserted). This can go into a log table or be returned to your application.
When to Use MERGE (and When Not To)
MERGE makes sense when you’re synchronizing tables and need multiple types of operations. It keeps your code cleaner and expresses your intent clearly, in that you’re maintaining consistency between two data sources.
However, if you’re only doing inserts, just use INSERT. If you’re only updating, stick with UPDATE. MERGE adds complexity, and there’s no benefit if you’re only performing one type of operation. Also, if your logic is getting complicated with lots of conditional clauses, you might be better off writing separate statements that are easier to understand and maintain.
The MERGE statement is a useful tool for data synchronization tasks. It reduces code, improves clarity, and handles the match-and-action logic efficiently. Just remember to test it thoroughly with your specific data patterns and volume to make sure it’s the right fit for your situation.