SQL MERGE Statement

Microsoft introduced the Merge statement in SQL Server 2008 to perform INSERT, DELETE, and UPDATE in a single statement. The SQL MERGE Statement is used to modify the data present in a target table based on data in the source table.

It means Merge statement in SQL Server joins the required table with the target table or view, and then performs the operations needed against them.

SQL MERGE Syntax

The syntax of the MERGE statement in SQL Server

-- Sql Server Merge Statement Syntax
MERGE [Destination table] AS ds
USING  [Source Table] AS st -- Include the columns Name
ON ds.[Common column] = st.[Common Column]
-- It's not about matching, You have to add the expressions
WHEN MATCHED THEN INSERT | DELETE | UPDATE
WHEN NOT MATCHED THEN INSERT | DELETE | UPDATE 

From the above merge syntax, it will check the expression after the matched keyword and operate. The basic rules to use this MERGE in SQL Server are:

  1. In the SQL Server Merge statement, you have to specify the table name or View on which you want to perform the insertion, deletion, or update operation.
  2. Data source that you want to join in the USING clause.
  3. ON clause is used to JOIN the Source and target table. Here, you have to specify the common column names that you want to use for joining both tables.
  4. You can perform the delete, insert, or update operation based on the result we get from the WHEN clause (WHEN NOT MATCHED, and WHEN MATCHED).
  5. In the same Matched clause, You cannot update the same variable more than once
  6. You have to use the semicolon (;) to terminate the Merge statement.
  7. If you use the @@ROWCOUNT after the SQL Server Merge statement then, it will return the number of rows inserted, deleted, and updated.

For this SQL Merge Statement example demonstration, we use two tables (Employee, and Merge table).

SQL MERGE STATEMENT 1

And [Merge table] table has eight records.

SQL MERGE Statement 2

SQL Merge Example

The following Sql Server merge query do the insert, delete, and Update operations on [Merge table] using the [Employee] table

-- SQL Server Merge Statement example
MERGE [Merge Table] AS mrg
USING (SELECT * FROM [Employee Table]) AS emp
ON mrg.Id = emp.Id
WHEN MATCHED AND mrg.[YearlyIncome] <= 50000 THEN DELETE
WHEN MATCHED AND mrg.[YearlyIncome] >= 80000 THEN 
   UPDATE SET mrg.[YearlyIncome] = mrg.[YearlyIncome] + 35000
WHEN NOT MATCHED THEN
INSERT ([FirstName]
           ,[LastName]
           ,[Education]
           ,[Occupation]
           ,[YearlyIncome]
           ,[Sales]
           ,[HireDate])
VALUES(emp.[FirstName]
           ,emp.[LastName]
           ,emp.[Education]
           ,emp.[Occupation]
           ,emp.[YearlyIncome]
           ,emp.[Sales]
           ,emp.[HireDate]);
GO
SQL MERGE Statement 3

First, we are using the [Merge table] in the Sql Server Merge clause. It means, we want to perform insert, delete, and update operations on Merge table.

MERGE [Merge Table] AS mrg

Next, we are using the [Employee table] in the SQL Server USING clause. It means we want to Join the [Merge table] with this data source.

USING (SELECT * FROM [Employee Table]) AS emp

It checks whether any employees whose Yearly Income is less than or equal to 50000 or not. If TRUE then, delete those records from [Merge table]. In real-time, you can also use this to delete the records from [Merge table] whose values are not matching with the Source table.

WHEN MATCHED AND mrg.[YearlyIncome] <= 50000 THEN DELETE

The next statement checks whether any employees whose Yearly Income is greater than or equal to 80000 or not. If TRUE, Update those records by adding 35000 to each record in [Merge table]. Please refer to the SQL UPDATE Statement article.

WHEN MATCHED AND mrg.[YearlyIncome] >= 80000 THEN 
   UPDATE SET mrg.[YearlyIncome] = mrg.[YearlyIncome] + 35000

Next statement will Insert the non matching records from [Employee] table to [Merge table]. Please refer SQL INSERT Statement article to understand the functionality.

WHEN NOT MATCHED THEN
INSERT ( .....
          ........)

SQL Merge Statement example 2

The following query is the best approach to use the merge statement. Whenever you are performing Merge operation, try to place the code inside the transaction (BEGIN TRAN ….. ROLLBACK TRAN). If any of the operations fails, it will rollback everything to the original.

-- SQL Server Merge Statement example
BEGIN TRAN;
MERGE [Merge Table] AS mrg
USING (SELECT * FROM [Employee Table]) AS emp
ON mrg.Id = emp.Id
WHEN MATCHED AND mrg.[YearlyIncome] <= 50000 THEN DELETE
WHEN MATCHED AND mrg.[YearlyIncome] >= 80000 THEN UPDATE SET mrg.[YearlyIncome] = mrg.[YearlyIncome] + 35000
WHEN NOT MATCHED THEN
INSERT ([FirstName]
           ,[LastName]
           ,[Education]
           ,[Occupation]
           ,[YearlyIncome]
           ,[Sales]
           ,[HireDate])
VALUES(emp.[FirstName]
           ,emp.[LastName]
           ,emp.[Education]
           ,emp.[Occupation]
           ,emp.[YearlyIncome]
           ,emp.[Sales]
           ,emp.[HireDate])
OUTPUT $action, inserted.*, deleted.*; -- Only to show the Actions 
ROLLBACK TRAN;
GO

In general, the OUTPUT clause returns a copy of the data that we inserted, deleted, and updated in our tables. That’s why we used the OUTPUT Clause with the SQL Server Merge Statement to show the query.

SQL MERGE Statement 4

The following merge code will give more control over the printed action result.

Sql Server Merge example 3

In this merge example, we are declaring the table variable @SqlMergeOutput with columns representing Delete, and Insert operations on each column.

For example, DelFirstName is to capture the delete operation on [FirstName], and InsLastName is to capture the Insert operation on [LastName]. Please refer SQL Delete Statement article.

-- SQL Server Merge Statement example
 	
DECLARE @SqlMergeOutput TABLE
(
  ActionType NVARCHAR(10),
  DelID INT,
  InsID INT,
  DelFirstName NVARCHAR(50),
  InsFirstName NVARCHAR(50),
  DelLastName NVARCHAR(50),
  InsLastName NVARCHAR(50),
  DelEducation NVARCHAR(50),
  InsEducation NVARCHAR(50),  
  DelOccupation NVARCHAR(50),
  InsOccupation NVARCHAR(50),
  DelYearlyIncome INT,
  InsYearlyIncome INT,
  DelSales FLOAT,
  InsSales FLOAT,
  DelHireDate DATE,
  InsHireDate DATE
);
MERGE [Merge Table] AS mrg
USING (SELECT * FROM [Employee Table]) AS emp
ON mrg.Id = emp.Id
WHEN MATCHED AND mrg.[YearlyIncome] <= 50000 THEN DELETE
WHEN MATCHED AND mrg.[YearlyIncome] >= 80000 THEN UPDATE SET mrg.[YearlyIncome] = mrg.[YearlyIncome] + 35000
WHEN NOT MATCHED THEN
INSERT ([FirstName]
           ,[LastName]
           ,[Education]
           ,[Occupation]
           ,[YearlyIncome]
           ,[Sales]
           ,[HireDate])
VALUES(emp.[FirstName]
           ,emp.[LastName]
           ,emp.[Education]
           ,emp.[Occupation]
           ,emp.[YearlyIncome]
           ,emp.[Sales]
           ,emp.[HireDate])
OUTPUT $action,  
    DELETED.ID,
    INSERTED.ID,
    DELETED.FirstName,
    INSERTED.FirstName,
    DELETED.LastName,
    INSERTED.LastName,
    DELETED.Education,
    INSERTED.Education,
    DELETED.Occupation,
    INSERTED.Occupation,
    DELETED.YearlyIncome,
    INSERTED.YearlyIncome,
    DELETED.Sales,
    INSERTED.Sales,
    DELETED.HireDate,
    INSERTED.HireDate 
  INTO @SqlMergeOutput;

SELECT * FROM @SqlMergeOutput;
SQL MERGE Statement 5

Comments are closed.