info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Merge
by BF (Principal Consultant; Architecture; Engineering)
2016-12-17








In T-SQL, you can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a Source with a Target, then perform DML actions against the Target based on the results of that join.



Data Prep: Create Table - Orders:

CREATE TABLE dbo.ORDERS
(OrderID INT,
CustomerID INT,
ItemDescription VARCHAR(100),
ItemCost INT,
PurchaseDate DATE NOT NULL)


Data Prep: Insert Data:

INSERT INTO dbo.ORDERS VALUES (1000, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS VALUES (1001, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS VALUES (1002, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS VALUES (1003, 2, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS VALUES (1004, 2, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS VALUES (1005, 3, 'Simulator1', 5, '2016-12-02')
INSERT INTO dbo.ORDERS VALUES (1006, 3, 'Simulator1', 5, '2016-12-02')
INSERT INTO dbo.ORDERS VALUES (1007, 3, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS VALUES (1008, 1, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS VALUES (1009, 1, 'Simulator2', 10, '2016-12-02')

SELECT * FROM dbo.ORDERS ORDER BY PurchaseDate ASC, OrderID ASC




Data Prep: Create Table - Orders_Stg:


CREATE TABLE dbo.ORDERS_STG
(OrderID INT,
CustomerID INT,
ItemDescription VARCHAR(100),
ItemCost INT,
PurchaseDate DATE NOT NULL)


Data Prep: Insert Data:

--INSERT INTO dbo.ORDERS_STG VALUES (1000, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_STG VALUES (1001, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_STG VALUES (1002, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_STG VALUES (1003, 2, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_STG VALUES (1004, 2, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_STG VALUES (1005, 3, 'Simulator2', 10, '2016-12-04')
INSERT INTO dbo.ORDERS_STG VALUES (1006, 3, 'Simulator1', 5, '2016-12-02')
INSERT INTO dbo.ORDERS_STG VALUES (1007, 3, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS_STG VALUES (1008, 1, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS_STG VALUES (1009, 1, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS_STG VALUES (1010, 1, 'Simulator2', 10, '2016-12-03')
INSERT INTO dbo.ORDERS_STG VALUES (1011, 1, 'Simulator2', 10, '2016-12-03')
INSERT INTO dbo.ORDERS_STG VALUES (1012, 1, 'Simulator2', 10, '2016-12-03')
INSERT INTO dbo.ORDERS_STG VALUES (1013, 1, 'Simulator2', 10, '2016-12-03')
INSERT INTO dbo.ORDERS_STG VALUES (1014, 1, 'Simulator2', 10, '2016-12-03')


SELECT * FROM dbo.ORDERS_STG ORDER BY PurchaseDate ASC, OrderID ASC




SELECT * FROM Orders
--This is the Target Table (decided by the Merge Query below)

SELECT * FROM Orders_STG
--This is the Source Table (decided by the Merge Query below)

Orders will be identical to Orders_Stg. The D(delete), U(update) and I(Insert) records are labeled below.




T-SQL Merge Query:

To Insert, Update and Delete data - i.e. Match a Target table from a Source table


BEGIN TRANSACTION
MERGE Orders o --Target
USING Orders_Stg os --Source
ON o.OrderID = os.OrderID
WHEN MATCHED AND o.PurchaseDate <> os.PurchaseDate
THEN
UPDATE SET o.ItemDescription = os.ItemDescription,
o.ItemCost = os.ItemCost,
o.PurchaseDate = os.PurchaseDate
WHEN NOT MATCHED
THEN
INSERT (OrderID, CustomerID, ItemDescription, ItemCost, PurchaseDate)
VALUES (os.OrderID,os.CustomerID,os.ItemDescription,os.ItemCost,os.PurchaseDate)
WHEN NOT MATCHED BY SOURCE THEN --Source is Orders_Stg tbl
DELETE --Delete from Orders tbl
OUTPUT $action,
inserted.OrderID AS SourceOrderID,
deleted.OrderID AS TargetOrderID;
COMMIT

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

Output:




Confirm tables are now merged

SELECT * FROM Orders
SELECT * FROM Orders_STG