SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

OUTPUT Clause Basics (For Inserts and Deletes)

By Mat Richardson,

When processing complex insert, update and delete statements it’s often useful to know exactly what has been changed, whether it’s for auditing purposes, or to return a value to a calling application (such as an ID, or other value following an insert, for example). The OUTPUT clause in T-SQL allows you to do just that, and it’s really, really straightforward….I’ll show you how!

Let’s start by creating a new table:

if object_id('tempdb..#outputexample') is not null drop table #outputexample;
go

create table #outputexample
(
       WidgetID int identity(1,1),
       WidgetName varchar(200),
       WidgetStatus varchar(10)
)

We will begin by populating this with a single row, and using the OUTPUT clause to return details on the row added:

insert #outputexample
output inserted.*
values
('Thingummybob','In Production')

In this really simplistic example you can see we added the OUTPUT clause directly after the insert statement, but before the actual change we wanted to make to the table.  You’ll also notice that we specified that we wanted to look at the inserted rows by prefixing the rest of the statement with the ‘inserted’ keyword.  In this example we followed this with * to return all columns (similar to SELECT *).

With this in mind (because SELECT * is naughty, ok?) we can be more specific about what we want to return:

insert #outputexample
output inserted.WidgetID, inserted.WidgetStatus
values
('Thingummybob','In Production')

And as you could expect, this would give us the WidgetID and WidgetStatus columns that were in the values added to the table. 

As you can see, the output clause is pretty much like applying a SELECT statement on the items that have been changed on the table in question. Let’s look at how this might work with delete.  For this example let’s add a number of items to the table so we’ve more to work with:

insert #outputexample
values
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued')

I’m now going to delete all ‘Discontinued’ items, and use the OUTPUT clause to return a list of the deletions:

delete from #outputexample
output deleted.*
where WidgetStatus = 'Discontinued'

Again, the OUTPUT clause is positioned directly after the delete statement but before the conditions for the delete itself, and the rest of the clause is prefixed with the ‘delete’ keyword (and another naughty *).  As you can see this is also pretty much like writing a ‘SELECT *’ from the deleted values from the table, and we could/should once again substitute the deleted.* for a comma separated list of column names, or even calculated columns with aliases if we wanted.

Another useful thing about this technique is that you can insert your output into another table if you wish.  Look at the example below:-

if object_id('tempdb..#outputexample') is not null drop table #outputexample;
if object_id('tempdb..#deleteditems') is not null drop table #deleteditems;
go

create table #outputexample
(
       WidgetID int identity(1,1),
       WidgetName varchar(200),
       WidgetStatus varchar(20)
)


create table #deleteditems
(
       WidgetID int,
       WidgetName varchar(200),
       WidgetStatus varchar(20),
       DeletedDateTime datetime
)

insert #outputexample
values
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued')

delete from #outputexample
output deleted.*, getdate()
into #deleteditems
where WidgetStatus = 'Discontinued'

select * from #deleteditems

In this case, I’ve created a table to store deleted item details, including a deleted date and time.  Using the output clause, I’ve been able to store the details of the deleted rows, including the date and time the deletion happened.  This is stupidly simple example, but something like this could be used for auditing purposes perhaps, if given more thought.

These were only very basic explanations of how the OUTPUT clause could be put to use, but as you can see it is quite a powerful command with multiple potential uses.  I encourage you to play around with this handy little technique and see what kinds of uses to which you can put it.

 
Total article views: 2061 | Views in the last 30 days: 6
 
Related Articles
ARTICLE

The OUTPUT Clause for INSERT & DELETE Statements

In this article, I will provide a set of examples to show case the use of OUTPUT clause for INSERT a...

FORUM

Output Clause

Using any other columns in output clause to insert into another table

BLOG

The OUTPUT clause

One of the features that I have been using quite a lot lately is the OUTPUT clause. This clause can...

BLOG

SQL Server- Output clause

Output Clause:- The SQL SERVER 2005 gives us an Output Clause which gives us the information about e...

BLOG

Instead Of Trigger and OUTPUT clause headache

It is known that you cannot get inserted identity values using OUTPUT clause when inserting to view ...

Tags
delete    
insert    
output    
t-sql    
 
Contribute