MERGE statement

----------MERGE STATEMENT--------------

--if there is an IDENTITY no need to pass ID value

MERGE C AS T USING B AS S ON T.ID=S.ID AND T.NAME=S.NAME
WHEN MATCHED THEN UPDATE SET T.NAME=S.NAME
WHEN NOT MATCHED THEN INSERT VALUES(NAME) OUTPUT inserted.*,deleted.*,$ACTION;

--C target table


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