OUTPUT Clause in SQL Server 2005

In previous versions of T-SQL you had to use a trigger to retrieve data from inserted, updated or deleted rows in tables which wasn't very straightforward.  SQL Server 2005 adds a new clause to T-SQL to simplify this task.  OUTPUT is this clause.

First suppose that I have a simple table like this:

CREATE TABLE MyTable

(

id INT IDENTITY PRIMARY KEY,

FullName nvarchar(20),

Age INT

)

OUTPUT clause works like a SELECT statement but its usage differs in INSERT, UPDATE and DELETE commands (I'll give examples to show its usage).

For INSERT commands you can simply put the OUTPUT clause inside the INSERT and use INSERTED table to get access to inserted data.  For example INSERTED.id refers to inserted id column in MyTable.  Below is an example of OUTPUT clause in INSERT command.

INSERT INTO MyTable

OUTPUT INSERTED.id AS InsertedID,

INSERTED.FullName AS InsertedFullName,

INSERTED.Age AS InsertedAge

VALUES ('Keyvan Nayyeri', 22)

Now I insert another row to use it in my samples.

INSERT INTO MyTable

OUTPUT INSERTED.id AS InsertedID,

INSERTED.FullName AS InsertedFullName,

INSERTED.Age AS InsertedAge

VALUES ('Gholi Javadzadeh', 24)

For UPDATE commands OUTPUT clause does the same job and retrieves data from updated rows but you can get access to old data with DELETED table and to new data with INSERTED table.  For example DELETED.id retrieves the old value of id column and INSERTED.id retrieves the new value of id column.  Below is an example of OUTPUT clause in UPDATE commands.

UPDATE MyTable

SET FullName = 'Javat Gholizadeh'

OUTPUT INSERTED.id AS ID,

DELETED.FullName AS OldFullName,

INSERTED.FullName AS NewFullName

WHERE id = 2

  

And finally for DELETE commands OUTPUT clause retrieves data from deleted rows.  Using DELETED table you have access to data from deleted rows.  For example DELETED.id refers to idcolumn of deleted row.  Here is an example of OUTPUT in DELETE command.

DELETE MyTable

OUTPUT DELETED.id AS DeletedID,

DELETED.FullName AS DeletedFullName,

DELETED.FullName AS DeletedAge

WHERE id = 2

Note that INSERTED and DELETED tables aren't physical tables.  They're just two logical tables.