T-SQL MERGE AND OUTPUT

1,

CREATE TABLE #t(id int IDENTITY(1,1) PRIMARY KEY,val int NOT NULL,testguid uniqueidentifier NOT NULL DEFAULT NEWID());GOINSERT #t (val)OUTPUT INSERTED.id,       INSERTED.val,   INSERTED.testguidVALUES (1),(2),(3)

 

2,

DELETE #tOUTPUT DELETED.*WHERE id IN (1,3);UPDATE #t SET val +=1OUTPUT DELETED.*,INSERTED.*WHERE id =2;SET IDENTITY_INSERT #t ON

 

3,

MERGE #t tUSING (VALUES (2,10,'Good'),(3,15,'Bad'),(4,20,'Ok')) s (id,val,ste)ON s.id = t.idWHEN MATCHED THEN  UPDATE SET val = s.val WHEN NOT MATCHED THEN INSERT (id,val) VALUES (s.id,s.val)OUTPUT $action,INSERTED.*,DELETED.*;

 

4,

INSERT #Audittable (action,oldval,id,newval,testguid,ste)SELECT * FROM (MERGE #t tUSING (VALUES (2,10,'Good'),(5,15,'Bad'),(4,30,'Ok')) s (id,val,ste)ON s.id = t.idWHEN MATCHED THEN  UPDATE SET val = s.val WHEN NOT MATCHED THEN INSERT (id,val) VALUES (s.id,s.val)OUTPUT $action,DELETED.val,INSERTED.*,s.ste) AS m (action,oldval,id, newval, testguid, ste);