T-SQL – Using the OUTPUT Clause to Get Identity Keys and Log Historical Data

The OUTPUT clause is a useful part of your T-SQL arsenal that has been added as of SQL Server 2005. You can use the OUTPUT clause in the same vein as @@IDENTITY, IDENT_CURRENT, or SCOPE_IDENTITY(), or even extend it to be a historical logging mechanism.

Unlike @@IDENTITY, or SCOPE_IDENTITY(), you need to populate a table rather than a variable when using the OUTPUT clause. This of course has its advantages as well in that you are able to log batch operations and easily store before/after historical information.

Further, Microsoft itself recommends using the OUTPUT clause rather than the @@IDENTITY or SCOPE_IDENTITY() since the latter two can cause problems if your queries use parallel execution plans. Have a look at the Microsoft Knowledgebase Article on the topic: http://support.microsoft.com/kb/2019779

If you find this article interesting, then have a look at my collection of articles covering various aspects of MS SQL Server.

How to Use the OUTPUT Clause

The OUTPUT clause uses the keywords inserted and deleted. Bottom line with these is that inserted means the value that was newly added to the table, while deleted means the old table value that was replaced by the inserted value.

You can use the OUTPUT clause to get information for INSERT, UPDATE, and DELETE statements. Just to mention this again, that also means batch INSERT, UPDATE, and DELETE statements, which is incredibly useful.

Examples of Using OUTPUT for INSERT, UPDATE, DELETE

In the code below I’m giving an example script that modifies test data in a table variable and logs the Identity of the affected rows in another temporary table.

Also, temporary logging table is used to save historical information of what each record looked like before it was modified and after it was modified.

You can paste and run this example code in your instance of SQL Server Management Services (SSMS).

DECLARE @ModificationTbl TABLE (
ID INT
,Modification VARCHAR (50)
,ModifiedValuePre VARCHAR(50)
,ModifiedValuePost VARCHAR(50)
)
DECLARE @tmpTbl TABLE (
ID INT IDENTITY
,TestValue VARCHAR(50)
)
INSERT INTO @tmpTbl (TestValue) 
OUTPUT inserted.ID,'INSERTED','',inserted.TestValue INTO @ModificationTbl
VALUES ('Test One')
INSERT INTO @tmpTbl (TestValue) 
VALUES ('Test Two')
INSERT INTO @tmpTbl (TestValue) 
VALUES ('Test Three')
INSERT INTO @tmpTbl (TestValue) 
VALUES ('Test Four')
UPDATE @tmpTbl
SET TestValue='update test two'
OUTPUT inserted.ID,'UPDATED',deleted.TestValue,inserted.TestValue INTO @ModificationTbl
WHERE ID IN (2,4)
DELETE FROM @tmpTbl 
OUTPUT deleted.ID,'DELETED',deleted.TestValue,'' INTO @ModificationTbl
WHERE ID=3
SELECT * FROM @ModificationTbl
SELECT * FROM @tmpTbl

Notice that in the example above I did a batch update of two records as part of the test. Both affected rows are correctly logged into the logging table, so you can see that using the OUTPUT clause is very useful in this respect. Naturally INSERT and DELETE statements can be handled in the same way.

For further reading, have a look at Microsoft’s OUTPUT clause documentation for SQL Server 2012 at: http://msdn.microsoft.com/en-us/library/ms177564.aspx