USE testDatabase; GO CREATE TABLE DataTable1 (ID int, DataValue varchar(50), Msg varchar(50)) CREATE TABLE DataTable2 (ID int, DataValue varchar(50), Msg varchar(50)) GO INSERT INTO DataTable1 VALUES (1, 'TDV1', 'Inserted'), (2, 'TDV2', 'Inserted') INSERT INTO DataTable2 VALUES (1, 'TDV3', 'Inserted'), (3, 'TDV4', 'Inserted') GO MERGE DataTable2 AS mainData USING (SELECT ID, DataValue FROM DataTable1) otherData ON (mainData.ID = otherData.ID) WHEN MATCHED THEN UPDATE SET Msg = 'MATCHED' WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, DataValue, 'TARGET NOT MATCHED') WHEN SOURCE NOT MATCHED THEN UPDATE SET Msg = 'SOURCE NOT MATCHED'; GO SELECT * FROM DataTable2 ORDER BY ID GO DROP TABLE DataTable1 DROP TABLE DataTable2 GO