1. it is an efficient way to perform multiple DML operations.
2. the most important advantage of MERGE statement is all the data is read and processed only once.
3. using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.
4. This is quite an improvement in performance of database query.
Example,
2. the most important advantage of MERGE statement is all the data is read and processed only once.
3. using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.
4. This is quite an improvement in performance of database query.
Example,
-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
ON C.CustID = CT.CustID
WHEN MATCHED THEN
UPDATE SET
C.CompanyName = CT.CompanyName,
C.Phone = CT.Phone
WHEN NOT MATCHED THEN
INSERT (CustID, CompanyName, Phone)
VALUES (CT.CustID, CT.CompanyName, CT.Phone)
CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);
--Synchronize source data with target
MERGE INTO dbo.tbl_Target AS t
USING dbo.tbl_Source AS s
ON t.id = s.id
WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
--Row exists and data is different
UPDATE SET t.name = s.name, t.qty = s.qty
WHEN NOT MATCHED THEN
--Row exists in source but not in target
INSERT VALUES (s.id, s.name, s.qty)
WHEN SOURCE NOT MATCHED THEN
--Row exists in target but not in source
DELETE OUTPUT$action, inserted.id, deleted.id
