Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

CREATE TABLE TempTable
 (
 ID INT IDENTITY(1 , 1)
 , 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 )

SELECT SCOPE_IDENTITY() AS LastInsertID

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.

DELETE FROM TempTable
OUTPUT Deleted.ID
 , Deleted.Code
 , Deleted.Name
 , Deleted.Salary
WHERE ID = 1

Conclusion

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: 16167 | Views in the last 30 days: 8
 
Related Articles
ARTICLE

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...

BLOG

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...

BLOG

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...

FORUM

output behavior

delete output into identity column

Tags
sql server 2005    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones