SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The OUPUT Command

By Dinesh Asanka,

OUTPUT Command in SQL Server 2005

This not about the output parameter that can be used in stored procedures. Instead, this is about returning data affected in a table with a new feature in SQL Server 2005.

SQL Server 2000

A simple question for you. If you want to retrieve the last inserted identity value, what do you do? Obviously SCOPE_IDENTITY() or @@IDENTITY will be your answer. There is a small different between these too, which I am not going to discuss right now. Even though both will satisfy the current requirement, I will use SCOPE_IDENTITY(), which is the correct one.

 , Code VARCHAR(25)
 , Name VARCHAR(50)
 , Salary Numeric(10 , 2)
INSERT INTO TempTable ( Code , Name , Salary )
  VALUES ( 'A001' , 'John' , 100 )
INSERT INTO TempTable ( Code , Name , Salary )
  VALUES ( 'A002' , 'Ricky' , 200 )


However, this will only be valid when you need the last inserted ID. A Problem arises when you need the last updated or deleted data. In SQL Server 2000, you don't have any other option other than writing a trigger or triggers to capture them via inserted and/or deleted tables.

SQL Server 2005

To satisfy the database developers' cry, SQL Server 2005 has an added feature called OUTPUT. Here is a short example:

INSERT INTO TempTable ( Code , Name , Salary )
OUTPUT Inserted.ID
  VALUES( 'A003' , 'Martin' , 300 )

The INSERT Statement not only inserts data to TempTable but also return the inserted ID value. Unlike, SCOPE_IDENTITY(), you have the ability of getting other affected values as well.

INSERT INTO TempTable ( Code , Name, Salary)
OUTPUT Inserted.ID
 , Inserted.Code
 , Inserted.Name
 , Inserted.Salary
  VALUES( 'A001' , 'Paul',400 )

The code above will return the ID, Code, Name and Salary fields as well.

The major improvement from the output command is that you have the ability of getting the affected values from an Update statement. In most cases you may need the information about the values that were there before they were changed, which you would get normally in an update trigger.

UPDATE TempTable
SET Salary = Salary * 1.10
OUTPUT Inserted.ID
 , Inserted.Code
 , Inserted.Name
 , Deleted.Salary PreviousSalary
 , Inserted.Salary NewSalary

The code above will return the ID, Code, Name along with previous salary and new salary and there is no need for an additional update trigger, which could have other performance issues.

Finally, let's look at deleting data. Even though it is the same as the above example, just for sake of completeness I will show this example.

 , Deleted.Code
 , Deleted.Name
 , Deleted.Salary


In SQL Server 2005, SCOPE_IDENTITY() and @@IDENTITY still work and there is no need to perform any modifications if you need the value of the Identity from an insert.

OUTPUT is another new feature in SQL Server 2005 to improve the productivity of Database developers. So use it whenever you think it is appropriate.

Total article views: 16185 | Views in the last 30 days: 6
Related Articles

Retrieving Identities on set-based inserts

Learn a foolproof way to get identity values for multi-row inserts with the OUTPUT clause in SQL Ser...


SQL Server Get Identity after Insert using SP

I thought I will share my experience how you can get identity value after insert using SQL Server. I...


SQL Server – How to Insert Explicit Values Into An Identity Column

By default, SQL Server automatically assigns a value to the Identity Column for each new row inserte...


output behavior

delete output into identity column

sql server 2005