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 id column 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.
Now playing: Paco De Lucia - Cositas Buenas
[advertisement] Axosoft OnTime 2008 is four developer tools in one: bug tracking, project wiki, feature management, and help desk. It manages your development process so developers can focus on coding. Installed or Hosted – Free Single-user license -- Free 30-day team trial.
9 Comments : 03.31.07
Feedbacks
Wow... Good One for the Beginners to learn about OUTPUT Clause.
good
I ran a performance test looking at capturing & usng the data instead of just returning it. I looped each test 1000 times. My goal was to compare OUTPUT INTO as a method to collect the identity of records actually inserted into a table variable vs. using a field in the inserted table as "marker" to identify rows this process added and then having to clear that marker field later.
While the actual insert into the inserted table does take longer with OUTPUT INTO (which makes sense, you are adding to two tables instead of just one), the overall process was much faster then the alternative (1360 ms vs. 8346 ms).
THANKS FO RUR HELP
"DELETED.FullName AS DeletedAge" was probably meant to read as "DELETED.Age AS DeletedAge".
#1
DotNetKicks.com
03.31.2007 @ 10:19 PM