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‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
