How to delete from source using MERGE command in SQL Server 2008?

Question

I am currently using the below merge code to migrate date from source to target. I have a new requirement to extend the below code to delete the record from source once an update/insert is performed on the target. Is this possible using merge(all the examples i see on the net had performing del/insert/update in the target not on the source)

    MERGE Target1 AS T USING Source1 AS S ON (T.EmployeeID = S.EmployeeID)  WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'      THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName) WHEN MATCHED      THEN UPDATE SET T.EmployeeName = S.EmployeeName WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'     THEN DELETE  ; 

Answer 1

You can use the output clause to capture the modified/inserted rows to a table variable and use that with a delete statement after the merge.

DECLARE @T TABLE(EmployeeID INT);  MERGE Target1 AS T USING Source1 AS S ON (T.EmployeeID = S.EmployeeID)  WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'      THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName) WHEN MATCHED      THEN UPDATE SET T.EmployeeName = S.EmployeeName WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'     THEN DELETE   OUTPUT S.EmployeeID INTO @T;  DELETE Source1 WHERE EmployeeID in (SELECT EmployeeID                      FROM @T);