SQL Server 2005 introduced the OUTPUT clause which has the ability to access the INSERTED and DELETED tables during DML actions. The OUTPUT clause can be added to your T-SQL scripts to write modified row data out to a table or return data back to the client.
The OUTPUT clause can be used for auditing and archiving modified rows. In this tutorial, I’ll walk through a few uses of the OUTPUT clause with different DML statements and examples. Copy and Paste the T-SQL below into SSMS.
USE tempdb
GO
IF OBJECT_ID('sampletbl', 'U') IS NOT NULL
DROP TABLE dbo.sampletbl;
IF OBJECT_ID('sampletbl_audit', 'U') IS NOT NULL
DROP TABLE dbo.sampletbl_audit;
GO
CREATE TABLE dbo.sampletbl (
ID INT IDENTITY(26, 1) PRIMARY KEY
,firstname VARCHAR(100)
,lastname VARCHAR(100)
)
GO
CREATE TABLE dbo.sampletbl_audit (
ID INT IDENTITY(1, 1) PRIMARY KEY
,fk INT
,WhatHappened VARCHAR(255)
,dt DATETIME
)
GO
DECLARE @IDs TABLE (ID INT)
/*INSERT the IDs of a records that were inserted into TV @IDs */
INSERT INTO dbo.sampletbl ( firstname, lastname )
OUTPUT INSERTed.ID
INTO @IDs
VALUES( 'Jim','Bob'),( 'Billy','Bob')
, ( 'Billy','Ray'),( 'Bubba','Bryar');
SELECT * FROM dbo.sampletbl;
SELECT * FROM @ids;
/*update record, display OUTPUT results */
SELECT * FROM dbo.sampletbl WHERE id = 28;
UPDATE dbo.sampletbl
SET firstname = 'Charles'
OUTPUT deleted.firstname [DELETED_firstname]
,inserted.firstname [INSERTED_firstname]
WHERE id = 28;
SELECT * FROM dbo.sampletbl WHERE id = 28;
/*INSERT OUTPUT results to audit table */
UPDATE dbo.sampletbl
SET firstname = 'Chuck'
OUTPUT inserted.ID
,'Name Change: ' + deleted.firstname
+ ' -> ' + inserted.firstname
,getdate()
INTO dbo.sampletbl_audit
WHERE id = 28;
SELECT *
FROM dbo.sampletbl_audit
drcdba.com 