How to get a value of identity column using OUTPUT - SQL Server

I have a 开发者_开发百科table and a trigger

create table test(id int not null identity(1,1) primary key, data int);
create trigger insteadTestInsert ON test INSTEAD OF INSERT 
AS
BEGIN
  INSERT INTO test(data) select data FROM inserted;
END;

When trigger is enabled, the following query

declare @tmp_table table(id int, int_val int);
insert into test(data) 
output inserted.* into @tmp_table
values (10);
select * from @tmp_table;

returns id = 0, int_val = 10 .

If I disable(or drop) the trigger, the query returns the proper value of id.

How to make OUTPUT insert proper results into table variable?

From MSDN

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

So the problem is that before your trigger was executed inserted didn't have id set.

Even if remove content of the trigger like this

create trigger insteadTestInsert on test instead of insert 
as
begin
    return;
end

you will see that inserted is still populated although nothing was inserted into the table. Basically inserted in output statement matches inserted inside of the trigger.

This one actually works.

declare @tmp_table table(seq int identity, id int, int_val int);
insert into test(data) 
output inserted.data into @tmp_table(int_val)
values (11),(12),(13);

update @tmp_table set id = seq + @@identity - @@rowcount
select * from @tmp_table;
select top 2 * from test order by id desc;

The restriction is that you must NOT have any other triggers on the table test that would "corrupt" the @@identity variable.

This is a hack, but it works:

declare @tmp_table table(id int, int_val int);
insert into test(data) 
output @@IDENTITY + 1, inserted.data into @tmp_table
values (10);
select * from @tmp_table;

See http://msdn.microsoft.com/en-us/library/ms190315.aspx for details on using @@IDENTITY.