Logging MERGE statement changes using OUTPUT

We can log all the information with the help $action variable. It can be used to log the merge action.  This variable will take one of three values:  “INSERT”, “UPDATE”, or “DELETE”.

Read about Merge statement:

http://bageshkumarbagi-msbi.blogspot.com/2018/11/merge-statement-in-sql-server-to-insert.html

See the example:

  

EMP_ID=2 address will be updated and EMP_ID 10 and 11 will be inserted.

See below sql script

declare @MergeLog table (ID int, MergeAction varchar(10)) MERGE Emp_Target T Using Emp_Source S ON T.EMP_ID = S.EMP_ID and T.EMP_NAME=S.EMP_NAME WHEN MATCHED and T.EMP_ADD != S.EMP_ADD THEN UPDATE SET T.EMP_ADD=S.EMP_ADD WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (S.EMP_ID,S.EMP_NAME,S.EMP_ADD) OUTPUT S.EMP_ID, $action into @MergeLog; SELECT MergeAction, count(*) FROM   @MergeLog GROUP BY MergeAction

  

See the records in the table

  

EMP_ID=2 address will be updated and EMP_ID 10 and 11 will be inserted.

Thanks.