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.
Let us go through the following example, which shall insert a single row into the table and display the result on the screen.
Use Northwind
Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
Insert Region
Output inserted.regionid,inserted.regiondescription into @ins
values(5,'NorthEast')
--Display Newly Inserted Value will be displayed
Select * from @ins
--Displays all values along with newly Inserted value will be displayed
Select * from Region
The above script is very easy to understand. We are declaring a variable “@ins,” which is of type “table” having two fields, “InsRegionID” (of type “int”) and “InsRegionDescription” (of type “nvarchar”). After that, we are inserting a row into the table "region" by using the “Insert” statement. Let us take a clear look at the “Insert” statement:
Insert Region
Output inserted.regionid,inserted.regiondescription into @ins
values(5,'NorthEast')
The above statement has its own details to understand. The row being inserted into the “region” table must be “cached” using the OUTPUT clause into the virtual table “@ins.” For that reason, we inserted the “OUTPUT” clause, just before the “values” clause. All the values which are going to be inserted will also have a copy to be saved in our virtual table “@ins.” As we are using an INSERT statement here, we need to use the reserved word “inserted.”
Finally, we execute the SELECT statement on the virtual table “@ins,” which directly retrieves all the values cached and displayed onto the screen
How about specifying explicit column names for the INSERT statement? The following example illustrates the same.
Use Northwind
Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
Insert Region(RegionID,RegionDescription)
Output inserted.regionid,inserted.regiondescription into @ins
values(6,'SouthNorth')
--Display Newly Inserted Value will be displayed
Select * from @ins
--Displays all values along with newly Inserted value will be displayed
Select * from Region
The above example is similar to the previous one except for the difference in the INSERT statement. In the previous example, we didn’t mention any explicit column names for the INSERT statement, but here we mentioned explicit column names. Even though it is not compulsory to work with explicit column names, it is highly recommended that you include them as part of the INSERT statement.
The previous section concentrated only on single INSERT statements. Let us now examine multiple INSERT statements employing the OUTPUT clause.
Let’s go through the script first.
Use Northwind
Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
Insert Region(RegionID,RegionDescription)
Output inserted.Regionid,inserted.RegionDescription into @ins
values(7,'NorthWest')
Insert Region(RegionID,RegionDescription)
Output inserted.Regionid,inserted.RegionDescription into @ins
values(8,'SouthEast')
Insert Region(RegionID,RegionDescription)
Output inserted.Regionid,inserted.RegionDescription into @ins
values(9,'NorthEast')
Insert Region
Output inserted.Regionid,inserted.RegionDescription into @ins
values(10,'SouthWest')
--Display Newly Inserted Value will be displayed
Select * from @ins
--Displays all values along with newly Inserted values will be displayed
Select * from Region
The above example is similar to the previous one except for the difference in the number of INSERT statements. In the previous example, we inserted a single row of information, but here we are inserting more than one row. We can specify as many INSERT statements as we want. Every INSERT statement adds a new row to the same virtual table “@ins.”
Finally, we execute the SELECT statement on the virtual table “@ins,” which directly retrieves all the values cached and displays them to the screen.
Let us go through the following example, which shall delete a single row from the table and display the result back onto the screen.
Use Northwind
Declare @del as table(delRegionID int,delRegionDescription nchar(50))
Delete region
Output deleted.regionid,deleted.regiondescription
into @del
Where regionid=7
--Display Deleted Value will be displayed
Select * from @del
The above script is very easy to understand. We are declaring a variable “@del,” which is of type “table” having two fields, “delRegionID” (of type “int”) and “delRegionDescription” (of type “nvarchar”). After that, we are deleting a row from the table "region" by using the “Delete” statement. Let us take a clear look at the “Delete” statement:
Delete region
Output deleted.regionid,deleted.regiondescription
into @del
Where regionid=7
The row being deleted from the “region” table must be “cached” using the OUTPUT clause into the virtual table “@del.” For that reason, we inserted the “OUTPUT” clause, just before the “Where” clause. All the values which are about to be deleted will also have a copy to be saved in our virtual table “@del.” As we are using the DELETE statement here, we need to use the reserved word “deleted.”
Finally, we execute the SELECT statement on the virtual table “@del,” which directly retrieves all the values cached and displays them on the screen.
Let us go through another example where we are deleting more than one row.
Use Northwind
Declare @del as table(delRegionID int,delRegionDescription nchar(50))
Delete region
Output deleted.regionid,deleted.regiondescription
into @del
Where regionid >4
--Display Deleted Value will be displayed
Select * from @del
If you can clearly observe the above script you must understand that the “where” clause looks a bit different. It works with more than one row now! We are trying to delete more than one row at a time, and it in turn saves the deleted information in the virtual table “@del.”
Finally, we execute the SELECT statement on the virtual table “@del,” which directly retrieves all the values cached and displays them on the screen.
The UPDATE statement internally performs two operations (deletion and insertion). So the OUTPUT clause needs to handle both of these two operations. Let us go through the following example.
Use Northwind
Declare @upd as table(UpRegionID int,UpRegionDescription nchar(50),UpNewRegionDescription nchar(50))
--Displays all values along with newly Updated value will be displayed
Select * from Region
The above script is very easy to understand and looks similar to the previous examples. We are declaring a variable “@upd,” which is of type “table” having three fields, “UpRegionID” (of type “int”), “UpRegionDescription” (of type “nvarchar”) and “UpNewRegionDescription” (of type nvarchar). After that, we are updating a row into the table "region" by using the “Update” statement. Let us take a clear look at the “Update” statement:
The column of a particular row being updated into the “region” table must be “cached” using the OUTPUT clause into the virtual table “@upd.” Here “UPDATE” means deleting the previous value from that column and inserting (or replacing) the new value into that column. According to the syntax of the UPDATE statement, the “OUTPUT” clause needs to be after the “SET” expression.
According to my needs, once the UPDATE statement is executed successfully, I would like to have both old and new values (of the same column “RegionDescription”) be stored in the virtual table “@upd.” Since we are updating here, we need to use both reserved words “inserted” and “deleted” along with the OUTPUT clause.
Finally, we execute the SELECT statement on the virtual table “@upd,” which directly retrieves all the values cached and displays them onto the screen
How about specifying more than one column name for the UPDATE statement? The following example illustrates the same.
Use Northwind
Declare @updCategory as table(UpCategoryID int,UpCategoryName nvarchar(15),NewUpCategoryName nvarchar(15),UpDescription ntext,NewUpDescription ntext)
Update categories
Set CategoryName='DairyProducts',Description='Cheese'
--Displays all values along with newly Updated value will be displayed
Select * from Categories
The above example is similar to the previous one except for the difference in the UPDATE statement. In the previous example, we didn’t mention more than one column for the UPDATE statement. But here we mentioned more than one column name. Now all the updates get stored in the cache (or virtual table). The rest is very similar to the previous example.
All the examples in this article were tested using SQL Server 2005 Enterprise Edition. Please note that I didn’t really test the examples on any of the other versions/editions of the similar suite of Microsoft products.
Any comments, suggestions, ideas, improvements, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
/ 15 
ASP.NET Export to Excel and Word using VB.NET and C#
