Wonders of the OUTPUT Clause in SQL Server 2005 - OUTPUT clause with single UPDATE statement
(Page 5 of 5 )
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))
Update region
Set RegionDescription='NorthernWest'
Output inserted.regionid,deleted.regiondescription,inserted.
regiondescription
into @upd
Where regionid=6
--Display Newly Updated Value will be displayed
Select * from @upd
--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:
Update region
Set RegionDescription='NorthernWest'
Output inserted.regionid,deleted.regiondescription,inserted.
regiondescription
into @upd
Where regionid=6
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'
Output inserted.categoryid,deleted.categoryname,inserted.categoryname,
deleted.description,inserted.description
into @updCategory
where categoryid=4
--Display Newly Updated Value will be displayed
Select * from @updCategory
--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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 7 
