Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server MERGE statement usage and examples


By:   |   Read Comments (1)   |   Related Tips: More > T-SQL

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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:

Person Id Last Name First Name Address Amount Mode
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

Sales2 Data

SELECT * FROM sales2		

Sales2 table output:

Person Id Last Name First Name Address Amount Mode
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

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.

Person Id Last Name First Name Address Amount Mode
11 Sales2LN2 Sales2FN2 Sales2ADD2 11 CASH

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).
Person Id Last Name First Name Address Amount Mode
11 Sales2LN2 Sales2FN2 Sales2ADD2 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
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

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		
Person Id Last Name First Name Address Amount Mode
11 Sales2LN2 Sales2FN2 Sales2ADD2 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
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

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jayendra Viswanathan Jayendra is a Project Leader with many years of IT experience. He has strong knowledge in software development and project management.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 12, 2018 - 9:05:47 AM - Jorge L Rosado Back To Top

 

 Which versions and editions of MS SQL can run Merge ?  Great tip!


Learn more about SQL Server tools