xxxxxxxxxx
MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
THEN update_statement
WHEN NOT MATCHED
THEN insert_statement
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Code language: SQL (Structured Query Language) (sql)
xxxxxxxxxx
MERGE sales.category t
USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = s.category_name,
t.amount = s.amount
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Code language: SQL (Structured Query Language) (sql)
xxxxxxxxxx
MERGE LoginTypes T
USING (SELECT 'System' as Description) S
ON(S.Description = T.Description)
WHEN NOT MATCHED BY TARGET
THEN INSERT(Description, CreatedTimestamp, LastUpdatedTimestamp)
VALUES('System', getdate(), getdate());
xxxxxxxxxx
/* Selecting the Target and the Source */
MERGE PRODUCT_LIST AS TARGET
USING UPDATE_LIST AS SOURCE
/* 1. Performing the UPDATE operation */
/* If the P_ID is same,
check for change in P_NAME or P_PRICE */
ON (TARGET.P_ID = SOURCE.P_ID)
WHEN MATCHED
AND TARGET.P_NAME <> SOURCE.P_NAME
OR TARGET.P_PRICE <> SOURCE.P_PRICE
/* Update the records in TARGET */
THEN UPDATE
SET TARGET.P_NAME = SOURCE.P_NAME,
TARGET.P_PRICE = SOURCE.P_PRICE
/* 2. Performing the INSERT operation */
/* When no records are matched with TARGET table
Then insert the records in the target table */
WHEN NOT MATCHED BY TARGET
THEN INSERT (P_ID, P_NAME, P_PRICE)
VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
/* 3. Performing the DELETE operation */
/* When no records are matched with SOURCE table
Then delete the records from the target table */
WHEN NOT MATCHED BY SOURCE
THEN DELETE
/* END OF MERGE */
xxxxxxxxxx
-- SQL SERVER MERGE
-- https://www.sqlshack.com/understanding-the-sql-merge-statement/
IF OBJECT_ID('TEMPDB..#SourceProducts') IS NOT NULL
BEGIN DROP TABLE #SourceProducts; END
IF OBJECT_ID('TEMPDB..#TargetProducts') IS NOT NULL
BEGIN DROP TABLE #TargetProducts; END
CREATE TABLE #SourceProducts(
ProductID INT,
ProductName VARCHAR(50),
ISDEPOT BIT
)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(1,'1-1',1)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(2,'1-0',1)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(3,'0-1',0)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(4,'0-0',0)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(88,'inserted in source',0)
CREATE TABLE #TargetProducts(
ProductID INT,
ProductName VARCHAR(50),
ISDEPOT BIT
)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(1,'1-1',1)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(2,'1-0',0)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(3,'0-1',1)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(4,'0-0',0)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(99,'inserted in target',0)
----
MERGE #TargetProducts AS TARGET
USING ( SELECT ProductID,ProductName, ISDEPOT
FROM #SourceProducts
) AS [SOURCE]
ON [Source].ProductID = Target.ProductID
-- INSERT
WHEN NOT MATCHED BY Target THEN
INSERT (ProductID,ProductName, ISDEPOT)
VALUES (Source.ProductID,Source.ProductName, Source.ISDEPOT)
-- UPDATE
WHEN MATCHED THEN UPDATE SET
TARGET.ISDEPOT = SOURCE.ISDEPOT
-- UPDATE
WHEN NOT MATCHED BY SOURCE
THEN UPDATE SET
TARGET.ISDEPOT = 1
--DELETE
--WHEN NOT MATCHED BY Source THEN
--DELETE
; --semicolon
---
SELECT 'SourceProducts',* FROM #SourceProducts
SELECT 'TargetProducts',* FROM #TargetProducts