Introduction to the SQL MERGE

Daily Knowledge Drop

The SQL MERGE statement can be used to perform an INSERT, UPDATE and/or DELETE all in one statement.

This post is just an introduction and does not address any performance issues or other bugs with the statement. See here (although out of date) for a list of potential bugs.

Example

In the below examples, we have a main Order table, which stores orders in our e-commerce system. However, orders placed via the mobile app are initially saved into a separate system - but then end up temporarily in the Order_Incoming table, to then be merged into the main Order table to give a single view of customer orders.

The below script will setup the various tables as well as some seed data:

IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Order')
BEGIN 
	DROP TABLE [Order]
END
 
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Order_Incoming')
BEGIN 
	DROP TABLE [Order_Incoming]
END
GO
  
CREATE TABLE [dbo].[Order](
  [OrderId] [int] IDENTITY(1,1) NOT NULL,
  [OrderNumber] [varchar](100) NULL,
  [OrderTotal] int
) 
 
CREATE TABLE [dbo].[Order_Incoming](
  [OrderNumber] [varchar](100) NULL,
  [OrderTotal] int
) 

-- create some orders
INSERT INTO [Order]
SELECT 'ORD0001', 32 UNION
SELECT 'ORD0002', 654 UNION
SELECT 'ORD0003', 104
SELECT 'ORDAPP0001', 99

-- incoming orders, 1 existing and 2 new
INSERT INTO [Order_Incoming]
SELECT 'ORDAPP0001', 110 UNION
SELECT 'ORDAPP0002', 43 UNION
SELECT 'ORDAPP0003', 200

The tables beforehand:

Order table:

Order_Incoming table:

Non-MERGE example

First, lets look at an example where we are doing an INSERT and separate UPDATE to ensure that the data in Order_Incoming is inserted or updated into Order.

-- Insert where the data DOESN'T exist
INSERT INTO [Order]
SELECT OrderNumber, OrderTotal
FROM Order_Incoming oi
WHERE NOT EXISTS(
	SELECT o2.OrderId
    FROM [Order] o2
    WHERE oi.OrderNumber = o2.OrderNumber) 

-- update where the data DOES exist
UPDATE [Order]
SET OrderTotal = oi.OrderTotal
FROM Order_Incoming oi
WHERE [Order].OrderNumber = oi.OrderNumber

After these statements, the Order table is updated correct and is as follows:

These two statements clearly work and result in the desired state - but we have two different statements here to ensure the data in Order table is updated and correct.

MERGE

MERGE statement

The format of the MERGE statement as follows:

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

A source and target table is specified, as well as how to match data between the two tables, and what action should be performed when:

• there is a match of data between the two tables
• there is no match, and the data exists in the target table
• there is no match, and the data exists in the source table

MERGE example

Let's rewrite the non-merge example from above to use the SQL MERGE statement:

MERGE [Order] o USING [Order_Incoming] oi
ON o.[OrderNumber] = oi.[OrderNumber]
WHEN MATCHED THEN 
	UPDATE SET o.OrderTotal = oi.OrderTotal
WHEN NOT MATCHED BY TARGET
	THEN 
	INSERT (OrderNumber, OrderTotal)
	VALUES (oi.OrderNumber, oi.OrderTotal);

Here we are specifying the Order table as the target, and the Order_Incoming table as the source - with the data matched on the OrderNumber column. Where there:

• is a match: update the Order table with the total from the Order_Incoming table
• is no match in the target Order table: insert data from Order_Incoming into Order
• is no match in the source Order_Incoming table: do nothing

This SQL statement has the same results as the INSERT and UPDATE statements as above - however is simpler to read and understand as well as being more concise.

Notes

Even though I've been working with SQL for approximately 20 years - I've only come across the MERGE statement recently. This would have been incredibly useful earlier in my career when all data access statements were written by hand. More recently though, all (most) data access I've done has been through Entity Framework, which abstracts most of the SQL statements away.

However knowing MERGE exists and what it can do, can be very useful in situations where you have to write the SQL manually. As with most things, the MERGE statement is not the solution to every problem, and should be benchmarked against other possible solutions (such as a INSERT + UPDATE).

References

SQL Server MERGE

Daily Drop 81: 25-05-2022

At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.

The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.