Wonders of the OUTPUT Clause in SQL Server 2005

(Page 1 of 5 )

This article mainly focuses on the new “output” clause introduced in Microsoft SQL Server 2005. In this article I shall also teach you about producing output for DML statements, which could not be done in any of the previous versions of SQL Server. I shall give examples of several issues including inserting, updating and deleting values. I've kept this article as simple as possible to give beginners a good start.

I assume that the readers of this article have some knowledge of RDBMS along with some exposure to either SQL Server 2000 or SQL Server 2005.

Introduction to the OUTPUT clause

Let us try to recollect our experiences with SQL Server 2000 or before. When we try to execute any DML statements, such as inserting a row, the output of that statement is a very simple message such as "One row is inserted." When you update a row, you are left with only the message "One row is updated;" the "delete" statement is similar.

From the above discussion, we can confirm that DML statements do not produce any results (apart from showing messages).  Let us consider a scenario where we show the inserted row to the user.  The user would be very happy. It would let him check whether or not he has provided the proper values. Showing the results is a long-anticipated feature, and it is now available through the OUTPUT clause in SQL Server 2005.   

Where does this OUTPUT clause get the inserted or deleted information? In fact, all deleted or inserted information gets stored in a virtual table (which of course needs to be created by us). By using a simple SELECT statement, we can retrieve all of the information available in the virtual table and display it to the screen. You can use the OUTPUT clause together with the inserted and deleted virtual tables, as you might use a trigger. But please be aware that the OUTPUT clause must be used with an INTO expression to fill a table.

Next: OUTPUT clause with single INSERT statement >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: Wonders of the OUTPUT Clause in SQL Server 2005

· Hello guys! This is an introductory to OUTPUT clause in SQL Server 2005. Try it...    · Thanks    · Hi, Nice article, but I was wondering if you have any hints on how to use this...   >>> Post your comment now!