Wonders of the OUTPUT Clause in SQL Server 2005 - OUTPUT clause with single DELETE statement
(Page 4 of 5 )
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.
Next: OUTPUT clause with single UPDATE statement >>
More MS SQL Server Articles
More By Jagadish Chaterjee
