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()
);
GO
INSERT #t (val)
OUTPUT INSERTED.id,
       INSERTED.val,
	   INSERTED.testguid
VALUES (1),(2),(3)

 

2,

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

 

3,

MERGE #t t
USING (VALUES (2,10,'Good'),(3,15,'Bad'),(4,20,'Ok')
) s (id,val,ste)
ON s.id = t.id
WHEN 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 t
USING (VALUES (2,10,'Good'),(5,15,'Bad'),(4,30,'Ok')
) s (id,val,ste)
ON s.id = t.id
WHEN 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);