Wednesday, 5 March 2008

SQL server 2005 : OUTPUT Clause

There are number of SQL server clauses that we have not even heard of, which results in unnecessary complexity in the stored procedures (SQL statements) written by us. Today I came across a very useful SQL clause that I would like to share with all of you.


Have you ever come across a situation where you need to delete data from one table and keep the deleted records in another table? Basically what I mean to say here is, many times, we require the changes in our physical data to be tracked some where. I.e. I want to keep track of affected records. I can store these affected records in either

A table data type variable: so that I can do some more manipulations on that.
A physical table: for maintaining log information

SQL Server 2005 provides a new clause known as "Output". Output operates on similar lines as that of a trigger i.e. it provides you the details about affected records in logical tables named deleted and inserted.

As mentioned, Output provides you access to inserted and deleted logical tables. The data that has been inserted into the table OR the data after update statement has been executed, is available in inserted logical table. Similarly, data deleted using delete statement OR as a result of updation(i.e. data that was there prior to updation) is available in deleted logical table

Let’s take one simple example now (simple because I feel that now you have a fair idea of what output is all about). We wish to update the name of an employee and we want to get both the old and the new value of the employee name:

UPDATE Employees
SET [Name] = 'Sanjeev'
OUTPUT inserted.EmployeeID, deleted.[name] as OldName, inserted.[Name] as NewName
WHERE [Name] = 'Gourav'

Following is the output of the above mentioned query:

You can see that we have the name that was updated, obtained from deleted logical table in column named "OldName" and the new value for the name in "NewName" from inserted logical table.

One last thing, which I discussed in the beginning of this article: storing the affected records in a table data type variable. Let’s take the example of updating the department IDs of employees:

-- Declare a temporary table to hold the updated records

DECLARE @TempTable Table
EmployeeID INT,
[Name] VARCHAR(50),
DepartmentID INT,
ManagerID INT

-- Update the table and store the affected records in @tempTable via output
UPDATE Employees
SET DepartmentID = 2
OUTPUT inserted.EmployeeID, inserted.[Name], inserted.DepartmentID, inserted.ManagerID INTO @TempTable
WHERE ManagerID = 2 AND DepartmentID = 3

-- Select all the records to see which were updated
SELECT * FROM @TempTable

Introduction of Output gives a lot of opportunities to developers like me to write a better and manageable code. I think you must have already thought of a couple of places where you can use this... So what are you waiting for..give it a shot!

Happy Programming!!!

kick it on

No comments:

Post a Comment