Cool NEW Feauture in SQL Server 2005 - OUTPUT Clause

by Nuno Pereira10. June 2010 16:59

 

A new Clause was introduced in SQL Server 2005 called "OUTPUT" that enables you to do some pretty cool stuff. For example, let's say you wanted to create an audit table and wanted it updated each time a query executes an INSERT or UPDATE statement (works with DELETE too) but you don't want to create a Trigger in your table. This is the perfect scenario where you could use the OUTPUT clause. In the example below, I created a table named employee and an audit table named emp_audit that I'll be using to store a copy of successfully affected row values from the employee table anytime an INSERT and/or UPDATE is executed:

 

 

CREATE TABLE employee(

      emp_id int IDENTITY(1,1),

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      Primary Key (emp_id)

);

 

CREATE TABLE emp_audit(

      emp_id int,

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      insert_date datetime

);

 

 

DECLARE @emp_id int

 

INSERT INTO employee(first_name, last_name, address1)

OUTPUT      inserted.emp_id, inserted.first_name, inserted.last_name,

            inserted.address1, getdate() INTO emp_audit

VALUES ('bobby', 'jones', '1234 main st')

 

/*

The OUTPUT clause will cause the SCOPE_IDENTITY() function to return the IDENTITY value of the emp_audit table (instead of employee) if the emp_audit table has an identity seed column.

*/

SELECT @emp_id = SCOPE_IDENTITY()

 

UPDATE employee

SET first_name = 'BOB', address1 = '300 broad st'

OUTPUT      inserted.emp_id, inserted.first_name, inserted.last_name,

            inserted.address1, getdate() INTO emp_audit

WHERE emp_id = @emp_id

 

SELECT *

FROM employee

WHERE emp_id = @emp_id

 

SELECT *

FROM emp_audit

WHERE emp_id = @emp_id

 

Another reason to use the OUTPUT clause is to retrieve a list of Identities for an INSERT, UPDATE, or DELETE statement that affects one or more records (especially useful when more than one record gets affected IMO).

 

CREATE TABLE employee2

(

      emp_id int identity(1,1),

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      PRIMARY KEY (emp_id)

);

 

CREATE TABLE vendors2

(

      vendor_id int identity(1,1),

      company_name varchar(55),

      address1 varchar(50),

      Primary Key (vendor_id)

);

 

INSERT INTO employee2 VALUES('michael', 'jackson', '111')

INSERT INTO employee2 VALUES('jane', 'doe', '222')

INSERT INTO employee2 VALUES('johnny', 'bravo', '333')

 

 

-- Variable Table that will store all the inserted vendor_id values

DECLARE @InsertedVendorIDs TABLE(vendor_id int)

 

INSERT INTO vendors2 (company_name, address1)

 -- The line below adds the inserted IDs to a variable table and is the recommended approach by Microsoft in SQL 2005 to retrieve identities since both Scope_Identity()and @@Identity could return incorrect identity values when a parallel query plan is generated (yup, it’s a bug in SQL Server click here to view the MS KB article)

OUTPUT inserted.vendor_id INTO @InsertedVendorIDs

SELECT      first_name + ' ' + last_name AS company_name, address1

FROM employee2

EXCEPT  -- <-- another cool query feature that I like but isn’t new. insert only records employees2 that don’t exist in vendors2

SELECT company_name, address1

from vendors2

 

-- retrieves all the inserted identity values that were created by the previous query

SELECT * FROM @InsertedVendorIDs

 

Tags: , ,

About the author

Started developing applications at the age of 13 utilizing VB 3.0 IDE. Around 2002, I moved over to VB.NET 1.0 and in 2003 moved over to C# 1.1.

.NET MCPD with over 11 years of experience in the real world.

 

Month List