Example: How to Use Merge Statement
In this example you will be creating a Target table named CricketTeams (destination table) and will populate it with the list of cricket playing nations. The second table named CricketTeams_UpdatedList will act as Source table, which has updated information about the cricket playing nations. Using MERGE statement you will be modifying the Target table named CricketTeams with the changes that of CricketTeams_UpdatedList Source table.
USE tempdb;
GO
IF OBJECT_ID (N’dbo.CricketTeams’, N’U’) IS NOT NULL
DROP TABLE dbo.CricketTeams;
GO
— TARGET TABLE
CREATE TABLE dbo.CricketTeams
(
CricketTeamID tinyint NOT NULL PRIMARY KEY,
CricketTeamCountry nvarchar(30),
CricketTeamContinent nvarchar(50)
)
GO
— INSERT DATA INTO TARGET TABLE
INSERT INTO dbo.CricketTeams VALUES
(1, ‘Australia’, ‘Australia’),
(2, ‘India’, ‘Asia’),
(3, ‘Pakistan’, ‘Asia’),
(4, ‘Srilanka’, ‘Asia’),
(5, ‘Bangaladesh’, ‘Asia’),
(6, ‘HongKong’, ‘Asia’),
(7, ‘U.A.E’, ‘Asia’),
(8, ‘England’, ‘Europe’),
(9, ‘South Africa’, ‘Africa’),
(10, ‘West Indies’, ‘North America’);
GO
SELECT * FROM CricketTeams
GO
IF OBJECT_ID (N’dbo.CricketTeams_UpdatedList’, N’U’) IS NOT NULL
DROP TABLE dbo.CricketTeams_UpdatedList;
GO
— SOURCE TABLE
CREATE TABLE dbo.CricketTeams_UpdatedList
(
CricketTeamID tinyint NOT NULL PRIMARY KEY,
CricketTeamCountry nvarchar(30),
CricketTeamContinent nvarchar(50)
)
GO
— INSERT DATA INTO SOURCE TABLE
INSERT INTO dbo.CricketTeams_UpdatedList VALUES
(1, ‘Australia’, ‘Australia’),
(2, ‘India’, ‘Asia’),
(3, ‘Pakistan’, ‘Asia’),
(4, ‘Srilanka’, ‘Asia’),
(5, ‘Bangaladesh’, ‘Asia’),
(6, ‘Hong Kong’, ‘Asia’),
(8, ‘England’, ‘Europe’),
(9, ‘South Africa’, ‘Africa’),
(10, ‘West Indies’, ‘North America’),
(11, ‘Zimbabwe’, ‘Africa’);
GO
/*
MERGE STATEMENT WHICH WILL PERFORM INSERT, UPDATE OR DELETE OPERATION BASED ON CONDITIONS MENTIONED WITHIN STATEMENT
*/
MERGE dbo.CricketTeams AS TARGET
USING dbo.CricketTeams_UpdatedList AS SOURCE
ON (TARGET.CricketTeamID = SOURCE.CricketTeamID)
WHEN MATCHED AND TARGET.CricketTeamContinent <> SOURCE.CricketTeamContinent OR
TARGET.CricketTeamCountry <> SOURCE.CricketTeamCountry
THEN UPDATE SET TARGET.CricketTeamContinent = SOURCE.CricketTeamContinent ,
TARGET.CricketTeamCountry = SOURCE.CricketTeamCountry
WHEN NOT MATCHED THEN
INSERT (CricketTeamID, CricketTeamCountry, CricketTeamContinent)
VALUES (SOURCE.CricketTeamID, SOURCE.CricketTeamCountry, SOURCE.CricketTeamContinent)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
INSERTED.CricketTeamID AS SourceCricketTeamID,
INSERTED.CricketTeamCountry AS SourceCricketTeamCountry,
INSERTED.CricketTeamContinent AS SourceCricketTeamContinent,
DELETED.CricketTeamID AS TargetCricketTeamID,
DELETED.CricketTeamCountry AS TargetCricketTeamCountry,
DELETED.CricketTeamContinent AS TargetCricketTeamContinent;
SELECT @@ROWCOUNT;
SELECT * FROM CricketTeams_UpdatedList
Important Points to Remember While Using MERGE Statement
- The MERGE statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator
- At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause
- If you are using @@ROWCOUNT at the end of MERGE statement, then it will return the total number of rows inserted, updated and deleted in the target table
- Use executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table
- MERGE Statement runs through data only once in the database which also improves performance
MERGE Statement Execution Results
Even though at the first glance the MERGE statement code looks very confusing, if you analysis it the logical implementation is very simple. Now let me explain what actually happees when we used the MERGE statement.
In the above snippet you could see that you have performed an UPDATE, DELETE and INSERT operation for records in CricketTeams (TARGET Table) based on certain conditions in CricketTeams_UpdatedList (SOURCE Table).
You could see that the record with CricketTeamID as “6” is UPDATED in CricketTeams (TARGET) Table. This is because in CricketTeams_UpdatedList SOURCE Table the value for CricketTeamCountry is “Hong Kong” where as in CricketTeams TARGET Table the value was “HongKong”. As there is a change in value for the CricketTeamCountry column in the SOURCE Table when compared to data in TARGET Table the record was UPDATED.
Next, you can also see a DELETE operation being performed on the CricketTeams TARGET Table for the record which had CricketTeamID as “7”. This has happens because there is no record in CricketTeams_UpdatedList SOURCE Table which has CricketTeamID as “7”. This means that the record is no longer required and its deleted from the CricketTeams TARGET table.
Finally, you can see an INSERT Operation which is being performed on CricketTeams TARGET Table. The new row is getting inserted into the CricketTeams TARGET table because, in CricketTeams_UpdatedList SOURCE Table there is new record with CricketTeamID as “11” appearing, which is not present in CricketTeams TARGET table. Hence the record is INSERTED into CricketTeams TARGET Table.
Conclusion
Using the MERGE statement a developer can reduce the complex TSQL code which is used to implement insert, update and delete logic when loading data into warehouse tables. The use of the Merge statement will also improve query performance.
]]>