OUTPUT clause in INSERT/UPDATE/DELETE statements

How do I use an INSERT statement's OUTPUT clause to get the identity value?

INSERT INTO URLDETSAMP(Name)

OUTPUT INSERTED.URLID

VALUES ('URLURLURLURLURLURLURL')

SELECT * FROM URLDETSAMP

ORDER BY URLID DESC

SELECT * FROM URLDETSAMP

ORDER BY URLID DESC

update URLDETSAMP SET Name='test'

output inserted.name  INTO URLDETSAMP(name)

WHERE URLID=47

SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table. More details on the restrictions of the OUTPUT clause and usage can be obtained from the SQL Server Books Online documentation.

Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem. Use of identity column as primary key in a table is a fairly common practice. Additionally, if you have references to the identity column you need to know the value explicitly before inserting the related row. This scenario gets complicated if multiple identity values are generated as a result of inserting multiple rows. In this case, there is no easy way to determine the values that were inserted without using a trigger to populate a temporary table with the generated identity values from the inserted table for example. To demonstrate this scenario, we can first see a trigger based implementation that works in SQL Server 2000 and 2005:

use tempdb;

go

create table itest ( i int identity not null primary key, j int not null unique );
go
create trigger insert_itest on itest after insert
as
begin
    insert into #new ( i, j )
    select i, j
      from inserted;
end
go
create table #new ( i int not null, j int not null );
insert into itest ( j )
select o.id from sysobjects as o;

-- Newly inserted rows and identity values:
select * from #new;
-- #new can be used now to insert into a related table:
drop table #new, itest;
go

This code can be re-written in SQL Server 2005 using the OUTPUT clause like below:

create table itest ( i int identity not null primary key, j int not null unique )
create table #new ( i int not null, j int not null)

insert into itest (j)
output inserted.i, inserted.j into #new
select o.object_id from sys.objects as o

select * from #new
drop table #new, itest;
go

Now from this example, you can see the integration of OUTPUT clause with existing DML syntax.

Another common scenario is auditing of data in a table using triggers. In this case, the trigger uses information from the inserted and updated tables to add rows into the audit tables. The example below shows code that uses OUTPUT clause in UPDATE and DELETE statements to insert rows into an audit table.

create table t ( i int not null );
create table t_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );

update t
   set i  = i + 1
output deleted.i, inserted.i into t_audit
 where i = 1;

delete from t
output deleted.i, NULL into t_audit
 where i = 2;

select * from t;
select * from t_audit;

drop table t, t_audit;
go

Hope you have now got an understanding of the OUTPUT clause in SQL Server 2005. It is a powerful feature that enables you to eliminate use of triggers in some cases or send results to client as part of the data modification operation efficiently.