info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
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
|
|
|
|
|
|
|
|
|
|