I have only just found out about the “MERGE” transact-SQL statement that was introduced in SQL Server 2008.
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
- Please ensure you test any script taken from my website on a test/development machine, before running on a production server. ↩