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

Posted in SQL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s