MERGE Transact-SQL statement example

I have only just found out about the “MERGE” transact-SQL statement that was introduced in SQL Server 2008.

From https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql:

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

To investigate this further, I have created a “MergeExample” database on my local instance, and created a Beverages table, and an Updates table to hold the new version of Beverages 1:

USE MergeExample
GO

IF OBJECT_ID('dbo.Beverages', 'U') IS NOT NULL DROP TABLE dbo.Beverages
GO

CREATE TABLE dbo.Beverages (
	BeverageID INT NOT NULL PRIMARY KEY, BeverageName VARCHAR(100) NOT NULL,
	DateAdded DATE NULL, DateUpdated DATE NULL)
GO

INSERT INTO dbo.Beverages (BeverageID, BeverageName, DateAdded)
VALUES	(1, 'Coffee', '2018-01-01'),
		(2, 'Tea', '2018-01-01'),
		(3, 'Water', '2018-01-01'),
		(4, 'Biscuits', '2018-01-01')
GO

IF OBJECT_ID('dbo.Updates', 'U') IS NOT NULL DROP TABLE dbo.Updates
GO

CREATE TABLE dbo.Updates (
	BeverageID INT NOT NULL PRIMARY KEY, BeverageName VARCHAR(100) NOT NULL)
GO

INSERT INTO dbo.Updates (BeverageID, BeverageName)
VALUES	(1, 'Ground Coffee'),
		(2, 'English Breakfast Tea'),
		(3, 'Water'),
		(5, 'Hot Chocolate')
GO

SELECT 'Before' AS Description, BeverageID, BeverageName, DateAdded, DateUpdated FROM dbo.Beverages;
SELECT BeverageID, BeverageName FROM dbo.Updates;
GO

Now here is the Merge statement in action:

--Synchronise the target table (Beverages) with the refreshed data from the source table (Updates)
MERGE dbo.Beverages AS TARGET
USING dbo.Updates AS SOURCE ON (TARGET.BeverageID = SOURCE.BeverageID)

--When the records are matched on Beverage ID but the name is different, update the record, also setting the DateUpdated field
WHEN MATCHED AND TARGET.BeverageName <> SOURCE.BeverageName
THEN
    UPDATE SET TARGET.BeverageName = SOURCE.BeverageName,
               TARGET.DateUpdated = GETDATE()

--When new Beverage records are received and don't match the target, insert the new records and set the DateAdded field
WHEN NOT MATCHED BY TARGET
THEN
    INSERT (BeverageID, BeverageName, DateAdded)
    VALUES (SOURCE.BeverageID, SOURCE.BeverageName, GETDATE())

--When a Beverage exists in the target but no update record has been received, we assume it has been deleted, so delete it from the Beverages table
WHEN NOT MATCHED BY SOURCE 
THEN
    DELETE

--The $action field (which is type nvarchar(10)) in the OUTPUT clause returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that has been performed on that row
OUTPUT $action,
       DELETED.BeverageID AS TargetBeverageID, DELETED.BeverageName AS TargetBeverageName,
       INSERTED.BeverageID AS SourceBeverageID, INSERTED.BeverageName AS SourceBeverageName;
GO

Now check the Beverages table after the changes:

SELECT 'After' AS Description, BeverageID, BeverageName, DateAdded, DateUpdated FROM dbo.Beverages
GO
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.
Updated: Thursday, 29th March, 2018 — 11:22 am

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.