How do I insert into a table and get back the primary key value?

Question

I have a primary key set up to auto increment.

I am doing multiple queries and I need to retrieve that primary key value to use as a foreign key in another table (IsIdentity = TRUE).

Is there any elegant way to get back the primary key value when I do an insert query? Right now I am requerying and getting the highest value in that column which seems really hacky.

Any suggestions?

Answer 1

If you are using SQL Server 2005 or later, you can use the OUTPUT clause.

create table T(   pk int identity primary key,   dat varchar(20) ); go  insert into T output inserted.pk values ('new item'); go  drop table T; 

The output can be directed to a table as well as to the client. For example:

create table T(   pk int identity primary key,   dat varchar(20) );  create table U(   i int identity(1001,1) primary key,   T_pk int not null,   d datetime ); go   insert into T output inserted.pk, getdate() into U(T_pk,d) values ('new item'), ('newer item'); go  select * from T; select * from U; go  drop table T, U; 

Beginning with SQL Server 2008, you can use "composable DML" for more possibilities.

Answer 2

insert into YourTable values (...)

get the new PK with scope_identity()

select scope_identity()