T‐SQL: MERGE statement - Single statement for Insert + Update + Delete operation

1.     Background

MERGE is a new feature of SQL Server 2008 that provides us an efficient way to perform multiple DML operations like INSERT, UPDATE & DELETE. It is commonly known as UPSERT (update/insert). In previous versions of SQL Server, we had to write separate statements for inserting, updating and deleting data based on certain conditions, but now, by using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

2.     Feature of Merge Statement?

One of the most important advantages of MERGE statement is all the data is read and processed only once. In previous versions of SQL Server, three different statements (INSERT, UPDATE or DELETE) has to be written to process three different activity, however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

3.     How MERGE statement works?

The MERGE statement performs insert, update, and delete operations within the same statement. We can specify a "Source" record set and a "Target" table and JOIN the Source & Target table with condition. Then specify the type of modification like if the row exists in the target table, UPDATE one or more columns; otherwise, INSERT the data into a new row.

4.     What are the advantages of MERGE statement?

·         All the data is read and processed only once in MERGE statement. All the update activities can be done in one pass of database table. It improves the performance of database query.

·         It reduces the complexity of your code or procedure.

·         After finishing the execution, we can audit all the transactions completed on the rows with using OUTPUT clause.

·         Synchronization of transactional tables.

5.     Syntax of MERGE statement

MERGE

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]

USING <table_source>

ON <merge_search_condition>

[ WHEN MATCHED [ AND <clause_search_condition> ]

THEN <merge_matched> ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

THEN <merge_not_matched> ]

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

THEN <merge_matched> ]

[ <output_clause> ]

[ OPTION ( <query_hint> [ ,...n ] ) ];

The MERGE syntax consists of five primary clauses:

·         The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.

·         The USING clause specifies the data source being joined with the target.

·         The ON clause specifies the join conditions that determine where the target and source match.

·         The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.

·         The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.

Source & Target Search Condition:

·         A matched pair consisting of one row from the target and one from the source. This is the result of the WHEN MATCHED clause.

·         A row from the source that has no corresponding row in the target. This is the result of the WHEN NOT MATCHED BY TARGET clause.

·         A row from the target that has no corresponding row in the source. This is the result of the WHEN NOT MATCHED BY SOURCE clause.

6.     Example of MERGE statement

--Creating tables Employee and Associate

CREATE TABLE dbo.Employee

( EmployeeID INT

,EmployeeName VARCHAR(50)

,CONSTRAINT Employee_PK PRIMARY KEY(EmployeeID)

)

CREATE TABLE dbo.Associate

( AssociateID INT

,AssociateName VARCHAR(50)

,CONSTRAINT Associate_PK PRIMARY KEY(AssociateID))

GO

--Inserting data into Employee table

INSERT dbo.Employee(EmployeeID, EmployeeName) VALUES(1, 'Charles');

INSERT dbo.Employee(EmployeeID, EmployeeName) VALUES(2, 'Belton');

INSERT dbo.Employee(EmployeeID, EmployeeName) VALUES(3, 'John David');

INSERT dbo.Employee(EmployeeID, EmployeeName) VALUES(4, 'Rick Bulmar');

INSERT dbo.Employee(EmployeeID, EmployeeName) VALUES(5, 'Allan Paul');

GO

--Inserting data into Associate table

INSERT dbo.Associate(AssociateID,AssociateName) VALUES(4,'Mark Bulmar');

INSERT dbo.Associate(AssociateID, AssociateName) VALUES(5, 'Steward Paul');

INSERT dbo.Associate(AssociateID, AssociateName) Values(6, 'Jonarthan');

INSERT dbo.Associate(AssociateID, AssociateName) Values(7, 'Vijit');

GO

--Checking the data into Employee table

SELECT * FROM Employee

--Checking the data into Associate table

SELECT * FROM Associate

Below is the MERGE with

Source Table: Associate

Target Table: Employee

Join condition : E.EmployeeID = A.AssociateID

Objectives

Obj‐1: If EmployeeID and AssociateID is not matched & Associate name starts with ‘J’ then insert the new record into the Employee table.

Obj‐2: If matched, then update the EmployeeName with the AssociateName.

Obj‐3: If EmployeeID and AssociateID is not matched & Associate name starts with ‘B’ then delete the employeeNames that starts with ‘B’

-- MERGE statement with the join conditions specified correctly.

BEGIN TRAN;

MERGE Employee AS E

USING Associate AS A

ON (E.EmployeeID = A.AssociateID)

WHEN NOT MATCHED BY TARGET AND A.AssociateName LIKE 'J%'

THEN INSERT(EmployeeID, EmployeeName) VALUES(A.AssociateID, A.AssociateName)

WHEN MATCHED

THEN UPDATE SET E.EmployeeName = A.AssociateName

WHEN NOT MATCHED BY SOURCE AND E.EmployeeName LIKE 'B%'

THEN DELETE

OUTPUT $action, inserted.*, deleted.*;

ROLLBACK TRAN; --For checking different scenarios, Rollbacking the TRAN

--COMMIT TRAN;

GO

After executing above MERGE statement. Following result comes with OUTPUT Clause.

Solution‐Obj‐1: As per the Obj‐1, if JOIN condition NOT MATCHED and AssociateName starts with ‘J’. Insert the new record into employee table. So “Jonarthan” is the only associate whose name start with ‘J’ and his AssociateID is 6 that is not available in Employee table. So MERGE inserted this record into the Employee table.

Solution‐Obj‐2: As per the Obj‐2, If JOIN condition matched then updates all the EmployeeName with AssociateName. AssocaiteID‐ 4 & 5 and EmployeeID 4 & 5 are available.

So EmployeeName “Rick Bulmar” has been replaced by AssociateName “Mark Bulmar” and “Allan Paul” is replaced by “Steward Paul”.

Solution‐Obj‐3: According to Obj‐3, if condition not matched and any employee has name started with ‘B’ then deletes the records. So “Belton” is the only employee whose name start with ‘B’ & his EmployeeID is 2 that ID is not available in Associate table. So MERGE deleted this record from the Employee table.

7.     Limitations

MERGE DML operation only implements with single target table or view. For example you can't insert, delete or update multiple tables using MERGE statement at once.

8.     Conclusion

After lot of research in T‐SQL by Microsoft, they introduce MERGE DML Statement. Using this we can combine INSERT, UPDATE, or DELETE operations on a destination table Based on results of a source table. After finishing the execution, we can audit all the transactions completed on the rows with using OUTPUT clause. And if you are writing huge procedure including lots of IF ELSE statements and validating always RECORDS EXIST OR NOT then Apply MERGE command.

‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐End of Document‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐