Fun with the SQL Output clause

The other day I was brushing up on my SQL skills by watching some training videos on Pluralsight. As I was watching, the presenter showed how the Output clause works. After playing around with it some more I decide to do this blog post.

So lets get started.  First lets create a temp table to work with.

create table #temptable (
    ID int not null identity primary key,
    FirstName nvarchar(100) null,
    LastName nvarchar(100) null,
    Cartoon nvarchar(100) null
)

Now lets insert some data using the output clause as part of the insert.  Note the script below is using the Table Row Constructor syntax.  This becomes extremely powerful when used with the output clause.

insert into #temptable
output inserted.ID, inserted.FirstName, inserted.LastName, inserted.Cartoon
values
('Fred','Flintstone', 'The Flinsones'),
('Wilma','Flintsone', 'The Flinsones'),
('Pebbles','Flintstone', 'The Flinsones'),
('Dino','', 'The Flinsones'),
('Barney','Rubble', 'The Flinsones'),
('Betty','Rubble', 'The Flinsones'),
('Bamm-Bamm','Rubble', 'The Flinsones'),
('George','Jetson', 'The Jetsons'),
('Jane','Jetson', 'The Jetsons'),
('Judy','Jetson', 'The Jetsons'),
('Elroy','Jetson', 'The Jetsons')

When this script is ran in SQL it will insert the rows into the temp table and return a result set that looks like this:

ID	FirstName	LastName	Cartoon
1	Fred	Flintstone	The Flinsones
2	Wilma	Flintsone	The Flinsones
3	Pebbles	Flintstone	The Flinsones
4	Dino		The Flinsones
5	Barney	Rubble	The Flinsones
6	Betty	Rubble	The Flinsones
7	Bamm-Bamm	Rubble	The Flinsones
8	George	Jetson	The Jetsons
9	Jane	Jetson	The Jetsons
10	Judy	Jetson	The Jetsons
11	Elroy	Jetson	The Jetsons

The inserting of data and the returned result set is done as a single transaction or round trip to the server.  The other benefit to this is the result set included the ID (identity) of each row.  Normally I would have re-queried the data to get this information.

Now let me show you an update using the output clause.  In the script below I am fixing the misspelling of Flintstone.

update #temptable
set Cartoon = 'The Flintstones'
output inserted.ID, deleted.Cartoon OldCartoonValue, inserted.Cartoon NewCartoonValue
where Cartoon = 'The Flinsones'

When the script is ran in SQL it will update the rows and return a result set that looks like this:

ID	OldCartoonValue	NewCartoonValue
1	The Flinsones	The Flintstones
2	The Flinsones	The Flintstones
3	The Flinsones	The Flintstones
4	The Flinsones	The Flintstones
5	The Flinsones	The Flintstones
6	The Flinsones	The Flintstones
7	The Flinsones	The Flintstones

Note that once again a single transaction to the server and a result set that references the ID’s that where updated along with the old cartoon value and the new cartoon value.  Keep reading below to learn more about the inserted and deleted column prefixes.

Now let me show you a delete with the output clause.  In this script below I am deleting any rows that have the last name of “Rubble”.

delete #temptable
output deleted.ID, deleted.FirstName, deleted.LastName, deleted.Cartoon
where LastName = 'Rubble'

When the script is ran in SQL it will delete the rows and return a result set that looks like this:

ID	FirstName	LastName	Cartoon
5	Barney	Rubble	The Flintstones
6	Betty	Rubble	The Flintstones
7	Bamm-Bamm	Rubble	The Flintstones

Not that once again a single transaction to the server and a result set that references the rows that where deleted.  Imagine if you used this in some sort of logging situation.

When using the output clause there are two special column prefixes to use:

• Deleted: Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed. (DELETED cannot be used with the OUTPUT clause in the INSERT statement.)
• Inserted: Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.(INSERTED cannot be used with the OUTPUT clause in the DELETE statement.)