Using The Output Clause

In a prior post,http://jahaines.blogspot.com/2009/09/output-clause-great-alternative-to.html , I described a great alternative to scope identity, which is the Output clause, http://technet.microsoft.com/en-us/library/ms177564.aspx. In this post I will describe how to use the Output clause in a manner other than getting identity values.  The Output clause is a new to SQL Server 2005.  The biggest benefit of this clause it allows a developer access to the DML pseudo tables, inserted and deleted.  Typically these pseudo tables are only available to DML triggers.  Now that I laid the groundwork, I will create our table structures.

USE [tempdb]

GO

IF OBJECT_ID('tempdb.dbo.t') IS NOT NULL

BEGIN

    DROP TABLE dbo.t;

END

GO

CREATE TABLE dbo.t(

Id INT IDENTITY(1,1) PRIMARY KEY,

Col CHAR(1)

);

IF OBJECT_ID('tempdb.dbo.DML') IS NOT NULL

BEGIN

    DROP TABLE dbo.DML;

END

GO

CREATE TABLE dbo.DML(

DML_Type CHAR(1),

Id INT,

Col CHAR(1)

);

Now that I have created our table structure, I can create our first DML transaction.  Our first DML transaction will be an insert statement where I use the values clause.  In this example and in the subsequent examples, I will be performing different DML transactions and inserting the affected row data and transaction type into the DML table.

--Insert a single value into the table dbo.t

INSERT INTO dbo.t (col) OUTPUT 'I',INSERTED.Id,INSERTED.Col INTO dbo.DML VALUES ('a');

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

*/

As you can see from the output above, I have used the output clause to successfully capture all the row data.  The next DML transaction I will demonstrate is a insert statement, using a select statement.

--Insert multiple values into the table dbo.t

INSERT INTO dbo.t (col) OUTPUT 'I',INSERTED.Id, INSERTED.Col INTO dbo.DML

SELECT 'b' UNION ALL

SELECT 'c'

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

I        2           b

I        3           c

*/

Next, I will demonstrate a delete statement.  I will be deleting all the rows, except id 2.

--Delete all ids but 2 from dbo.t

DELETE FROM dbo.t OUTPUT 'D',DELETED.Id,DELETED.Col INTO dbo.DML

WHERE id <> 2

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

I        2           b

I        3           c

D        1           a

D        3           c

*/

In he last example, I will be demonstrating an Update statement.  One thing to remember about an Update statement is the Output Clause has access to both the INSERTED and DELETED pseudo tables. It should be noted that I am only getting data from the INSERTED table.

--Update id = 2 the table dbo.t

UPDATE dbo.t

SET Col = 'E'

OUTPUT 'U', INSERTED.Id,INSERTED.[Col] INTO dbo.DML

WHERE Id = 2

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

I        2           b

I        3           c

D        1           a

D        3           c

U        2           E

*/

There you have it.  The Output clause is a very powerful clause that allows developers a method to obtain data that was once difficult to get.  This clause can be used in a lot of different scenarios and situations, but I leave it to you to find out how this can be used in your environment.

Happy Coding.