output clause

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