MERGE SQL Server

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,

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