SQL MERGE Statement

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

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

Syntax

The syntax of the SQL Server MERGE statement to perform inserts, updates and deletes rows in the target table.

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 syntax, it will check the expression after the matched keyword and operate. The basic rules to use this are:

  1. In the 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 this statement.
  7. If you use the @@ROWCOUNT after the Merge statement then, it will return the number of rows inserted, deleted, and updated.

For this SQL Server Merge Statement example demonstration, we use two tables (Cust and Rest).

First Table records 1

And Rest table has eight records.

Second Table Rows 2

SQL Server Merge Example

The following query does the insert, delete, and Update operations on Rest using the Cust table.

MERGE [Rest] AS mrg
USING (SELECT * FROM [Cust]) AS emp
ON mrg.EmpId = emp.EmpId
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 above table in the Sql Server Merge clause. It means, we want to perform insert, delete, and update operations on Rest.

Next, we are using the Cust in the SQL Server USING clause. It means we want to Join the Rest with this data source.

USING (SELECT * FROM [Cust]) 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 Rest. In real-time, you can also use this to delete the records from Rest whose values are not matching with the Source.

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 matched then Update set of those records by adding 35000 to each record in Rest table. Again, Please refer to the 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 Cust table to Rest. Please refer to the 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.

BEGIN TRAN;
	MERGE Rest AS mrg USING (SELECT * FROM [Cust]) AS ct
	ON mrg.EmpId = ct.EmpId
	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(ct.[FirstName]
           ,ct.[LastName]
           ,ct.[Education]
           ,ct.[Occupation]
           ,ct.[YearlyIncome]
           ,ct.[Sales]
           ,ct.[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 Merge Statement to show the query.

SQL MERGE Statement 4

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

Example 3

In this merge example, we are declaring the table variable @Example 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 to the Delete Statement article.

DECLARE @Example TABLE
(
  ActionType NVARCHAR(10),
  DelID INT,
  InsID INT,
  DelFirst NVARCHAR(50),
  InsFirst NVARCHAR(50),
  DelLast NVARCHAR(50),
  InsLast NVARCHAR(50),
  DelEdu NVARCHAR(50),
  InsEdu NVARCHAR(50),  
  DelOcc NVARCHAR(50),
  InsOccu NVARCHAR(50),
  DelIncome INT,
  InsIncome INT,
  DelSales FLOAT,
  InsSales FLOAT,
  DelHire DATE,
  InsHire DATE
);
MERGE [Rest] AS mrg
USING (SELECT * FROM [Cust]) AS emp
ON mrg.EmpId = emp.EmpId
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.EmpID,
    INSERTED.EmpID,
    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 @Example;

SELECT * FROM @Example;
SQL MERGE Statement 5

Comments are closed.