T-SQL New features in SQL Server 2005 - Part 4

Have you ever come across a situation where you need to delete data from one table and keep the deleted records in another table? or insert data in one table and also add the new rows at the same time to another? Weird situation...but this is what I had to do while writing a stored procedure that operates on some physical table and also keep track of affected records in another table(a table data type variable, to be precise) so that I can do some more manipulations on that. This approach, of keeping two tables(a physical and a table type variable)in sync with each other, is definitely not a clean approach but comes in handy when you know that you will be working on a very small subset of a table containing thousands of rows. Now remember, I had to do all manipulations in the stored procedure itself and SQL Server 2000 or earlier version, do not provide you any option so that I can affect two tables with one query.By using Output clause, not only you can output the data into another table, but also send it to the client as if it were a select command.

Output Clause

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.

Lets take up a quick example to understand what I have been talking about:

Suppose we have a table called Employees and we wish to insert a new record into it and also get the ID of the inserted record, which we want to return to the client of our stored procedure.

Lets assume, we have a column named EmployeeID in the table. The query we will write to insert the record looks like following:

INSERT INTO Employees([Name],DepartmentID, ManagerID) 
VALUES('Alec', 1, 2)

To get the value of identity column, we can use well known @@Identity as follows:

INSERT INTO Employees([Name],DepartmentID, ManagerID) 
VALUES('Alec', 1, 2)
SELECT @@Identity

This will output the inserted ID. However, while working on tables having triggers defined on it, it is better to use Scope_Identity() since it operates in the current scope. The modified statement will look as follows:

INSERT INTO Employees([Name],DepartmentID, ManagerID) 
VALUES('Alec', 1, 2)
SELECT Scope_Identity()

In both the cases, however, you can see that we are required to write two statements: one for performing the operation(insert the record) and another to get the affected/inserted ID. Using the Output clause, makes it possible to insert the record as well as return the ID in same SQL statement:

INSERT INTO Employees([Name],DepartmentID, ManagerID) 
OUTPUT inserted.EmployeeID
VALUES('Alec', 1, 2)

Pretty neat! You can see that all the above three statements produces the same result, but using output saves you from writing an extra statement to fetch the ID of inserted record. It is also worth noting that the Output clause give you the value before any triggers have fired on the table i.e. if there is an "instead of" trigger defined, and the record is not actually inserted, you will still get the ID of the inserted record.
Lets look at an interesting problem now. Suppose that you wish to update data in certain table as per the condition specified in where clause and you also wish to return the affected records to the client for some reason. Output does just that.

Lets look the table first:



Now, we wish to update the departmentID to 2 for all those employees who are reporting to EmployeeID 2(i.e. employeeID 4, 5 and 8) and have department ID set as 3 and also return these records to client of our stored procedure. Once these records are updated, we have absolutely no way to get the details about which records were updated. To do this, we can possibly store all the IDs(based on our condition) into some table variable before firing the update command and then use the previously saved IDs to get which records were updated. Another, more efficient, way can be: Output

UPDATE Employees
    SET DepartmentID = 2
OUTPUT inserted.EmployeeID, inserted.[Name], inserted.DepartmentID, inserted.ManagerID
    WHERE ManagerID = 2 AND DepartmentID = 3

Following is the output of above mentioned query:


As mentioned earlier, 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.

Lets 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] = 'Alex'
OUTPUT inserted.EmployeeID, deleted.[name] as OldName, inserted.[Name] as NewName
    WHERE [Name] = 'Alec'

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. Cool!!! Isn't it?

One last thing, which I discussed in the beginning of this article: storing the affected records in a table data type variable. Lets 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!