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);