SQL Server MERGE statement usage and examples

Problem

In the world of software development, SQL Server developers face issues when it comes to having to perform multiple Insert and Update statements. To overcome this problem there is an option to use the MERGE statement in SQL Server that allows you to do this all at once. This tip will show the usage of the MERGE statement over separate INSERT and UPDATE statements in SQL.

Solution

We will create some sample tables and data and then show how to do an INSERT and UPDATE then show how to use MERGE to do the same thing with one statement.

Create Sample Data

The following code with DDL and DML statements is used to prepare data for this example. Two tables, Sales1 and Sales2, will be created:

• Table Sales1 is made up of:
  • Six fields – PersonId, LastName, FirstName, Address, Amount, Payment_Mode
  • Five rows – PersonId values 11-15
• Table Sales2 is made up of:
  • Six fields – PersonId, LastName, FirstName, Address, Amount, Payment_Mode
  • Six rows – PersonID values 1-5, 11. (with one matching record – PersonID 11).

-- script 1 - create sample tables and data
IF OBJECT_ID ('sales1','U') IS NOT NULL
    DROP TABLE sales1;
GO
/****** CREATE Sales1 ONE TABLE ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sales1](
            [PersonID] [float] NULL,
            [LastName] [nvarchar](255) NULL,
            [FirstName] [nvarchar](255) NULL,
            [Address] [nvarchar](255) NULL,
            [Amount] [float] NULL,
            [Payment_Mode] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** CREATE Sales2 ONE TABLE ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ('sales2','U') IS NOT NULL
    DROP TABLE sales2;
GO
CREATE TABLE [dbo].[Sales2](
            [PersonID] [float] NULL,
            [LastName] [nvarchar](255) NULL,
            [FirstName] [nvarchar](255) NULL,
            [Address] [nvarchar](255) NULL,
            [Amount] [float] NULL,
            [Payment_Mode] [nvarchar](255) NULL
) ON [PRIMARY]
GO
 
INSERT INTO [dbo].[Sales1]
           ([PersonID]
           ,[LastName]
           ,[FirstName]
           ,[Address]
           ,[Amount]
           ,[Payment_Mode])
Values
('11','Sales1LN1','Sales1FN2','Sales1ADD2','11','CASH'),
('12','Sales1LN2','Sales1FN2','Sales1ADD2','12','CASH'),
('13','Sales1LN2','Sales1FN2','Sales1ADD2','13','CASH'),
('14','Sales1LN2','Sales1FN2','Sales1ADD2','14','CASH'),
('15','Sales1LN2','Sales1FN2','Sales1ADD2','15','CASH')
 
INSERT INTO [dbo].[Sales2]
           ([PersonID]
           ,[LastName]
           ,[FirstName]
           ,[Address]
           ,[Amount]
           ,[Payment_Mode])
Values
('1','Sales2LN1','Sales2FN2','Sales2ADD2','11','CASH'),
('2','Sales2LN1','Sales2FN2','Sales2ADD2','12','CASH'),
('3','Sales2LN2','Sales2FN2','Sales2ADD2','13','CASH'),
('4','Sales2LN2','Sales2FN2','Sales2ADD2','14','CASH'),
('5','Sales2LN2','Sales2FN2','Sales2ADD2','15','CASH'),
('11','Sales2LN2','Sales2FN2','Sales2ADD2','11','CASH')

Sales1 Data

SELECT * FROM sales1

Sales1 table output:

Sales2 Data

SELECT * FROM sales2

Sales2 table output:

The goal is to get Sales1 updated with any differences in Sales2 and to also add rows to Sales1 that only exist in Sales2.

INSERT and UPDATE Usage (without MERGE)

In this section, the code performs an UPDATE operation to match data between Sales1 and Sales2 tables using PersonID. We know that PersonId 11 is in both tables, so we will focus on that data.

UPDATE tab1
SET tab1.LastName = tab2.LastName ,
    tab1.FirstName = tab2.FirstName ,
    tab1.address = tab2.address
FROM Sales1 AS tab1
    INNER JOIN
    Sales2 AS tab2
        ON tab1.personid = tab2.personid;

Now let’s look at what the data looks like after the update.

SELECT * FROM sales1 WHERE PersonID = 11

In the table below (Sales1) we observed that the LastName, FirstName, Address in the Sales 1 are all updated to match value(s) (PersonId 11 in this case) from Sales2.

Following the update to Sales1 for matching records, the code below performs an INSERT operation of non-matching rows from Sales2 into Sales1.

INSERT INTO sales1
SELECT *
FROM sales2
WHERE personid NOT IN ( SELECT personid FROM sales1 );

Now let’s look at what the data looks like after the update and insert.

SELECT * FROM sales1

The below is the output at the end of INSERT and UPDATE operations on the Sales1 table. Note that the Sales1 table now contains 10 records:

• Retaining all the records that were initially inserted during setup
• Updated with values from Sales2 table for a matching PersonId in Sales1 (achieved through an UPDATE operation).
• Inserting 5 records from Sales2 table where PersonId was not present in Sales1 table (achieved through an insert operation).

Using the MERGE Statement

Before we run this, rerun Script 1 above to drop the tables and recreate the original data.

The MERGE statement usually involves two tables, the Source (Sales2 in our example) and the Target tables (Sales1) with the operation performed based on a common column – PersonID in this illustration.

The below code is an illustration for using the MERGE statement to perform more than one operation.

Merge into sales1 as tab1 
using(select * from Sales2) as tab2 
   on tab1.PersonID=tab2.PersonID 
when matched then 
   update set 
   tab1.lastname=tab2.lastname, 
   tab1.FirstName=tab2.FirstName, 
   tab1.Address=tab2.Address, 
   tab1.Amount=tab2.Amount, 
   tab1.Payment_Mode=tab2.Payment_Mode 
when not matched then 
   insert values(tab2.PersonID,tab2.LastName,tab2.FirstName,tab2.Address,tab2.Amount,tab2.payment_mode); 

If you notice in the above example the columns mapped are lastname, firstname, address, amount and payment_mod. The first set is UPDATE statement with a join using sales1.personid with sales2.personid. The next set of the statement is the INSERT, the insert will use the same columns as used in update and will insert all the non-matching rows between sales1 and sales2 table.

After running the above code, let’s look at the merged data.

SELECT * FROM sales1

We observe that in the MERGE statement above, both INSERT and UPDATE operations are performed in a single step, that was previously performed as two separate (UPDATE / INSERT) operations.

Next Steps

• Check out these other T-SQL tips on MSSQLTips.com.
• Check out these additional resources:
  • Using MERGE in SQL Server to insert, update and delete at the same time
  • Resolving the MERGE statement attempted to UPDATE or DELETE the same row more than once error
  • Using the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions
  • Use Caution with SQL Server’s MERGE Statement

Jayendra Viswanathan

Jayendra is a Project Leader for a IT Service company. Jayendra has over 15 years of IT project experience in software development, software support, production support and product support. He has specialized in Microsoft SQL Server, Microsoft Access, the Microsoft Business Intelligence suite earning a Microsoft certification. Jayendra has expertise in IT Project Management as well. Jayendra’s personal interests are in the stock market.