Question about OUTPUT clause

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @.insertedRecords

values('row 20')

select ID from @.insertedRecords

TIA,

Barkingdog

.

Sure can. Did your attempts not work? Here is a script to demo:

create table testTable
(
testTableId int identity primary key,
value varchar(10) unique
)
go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
values('row 20')

select scope_identity()
select * from @.insertedRows

go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
select 'row 21'
union all
select 'row 22'

select * from @.insertedRows

|||

I haven't encoutnered any problems but I was just wondering if my original thought was correct.

Thanks.

Barkingdog

sql