Microsoft SQL Server MERGE Merge Example - Synchronize Source And Target Table
Example
To Illustrate the MERGE Statement, consider the following two tables -
-
dbo.Product : This table contains information about the product that company is currently selling
-
dbo.ProductNew: This table contains information about the product that the company will sell in the future.
The following T-SQL will create and populate these two tables
IF OBJECT_id(N'dbo.Product',N'U') IS NOT NULL
DROP TABLE dbo.Product
GO
CREATE TABLE dbo.Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)
IF OBJECT_id(N'dbo.ProductNew',N'U') IS NOT NULL
DROP TABLE dbo.ProductNew
GO
CREATE TABLE dbo.ProductNew (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)
INSERT INTO dbo.Product VALUES(1,'IPod',300)
,(2,'IPhone',400)
,(3,'ChromeCast',100)
,(4,'raspberry pi',50)
INSERT INTO dbo.ProductNew VALUES(1,'Asus Notebook',300)
,(2,'Hp Notebook',400)
,(3,'Dell Notebook',100)
,(4,'raspberry pi',50)
Now, Suppose we want to synchoronize the dbo.Product Target Table with the dbo.ProductNew table. Here is the criterion for this task:
-
Product that exist in both the dbo.ProductNew source table and the dbo.Product target table are updated in the dbo.Product target table with new new Products.
-
Any product in the dbo.ProductNew source table that do not exist in the dob.Product target table are inserted into the dbo.Product target table.
-
Any Product in the dbo.Product target table that do not exist in the dbo.ProductNew source table must be deleted from the dbo.Product target table. Here is the MERGE statement to perform this task.
MERGE dbo.Product AS SourceTbl
USING dbo.ProductNew AS TargetTbl ON (SourceTbl.ProductID = TargetTbl.ProductID)
WHEN MATCHED
AND SourceTbl.ProductName <> TargetTbl.ProductName
OR SourceTbl.Price <> TargetTbl.Price
THEN UPDATE SET SourceTbl.ProductName = TargetTbl.ProductName,
SourceTbl.Price = TargetTbl.Price
WHEN NOT MATCHED
THEN INSERT (ProductID, ProductName, Price)
VALUES (TargetTbl.ProductID, TargetTbl.ProductName, TargetTbl.Price)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, INSERTED.*, DELETED.*;
Note:Semicolon must be present in the end of MERGE statement.
