insert into sql server table and get back primary key

Question

i have a primary key set up to auto increment.

i am doing multiple queries and i need to retrieve that primary key to use as a foreign key in another table (is identity = true)

is there any elegant way to get back the primary key 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

insert into YourTable values (...)

get the new PK with scope_identity()

select scope_identity()

Answer 2

holy crap!!!

just call SCOPE_IDENTITY() function:

insert into your_talble(col1,col2) values('blah','more blah')
select scope_identity()

because selecting highest value will return error if any other statement make an insert. the function scope_identity() returns the identity created in current context (that is by your statement)

Answer 3

SCOPE_IDENTITY() is probably what you want. It returns the ID of the last record inserted by the same code context in which it executes.

IDENT_CURRENT('tablename') is subject to concurrency issues. That is, there's no guarantee that another record won't be inserted between the INSERT and the call to IDENT_CURRENT.

I must confess, I'm not sure to what source of amazement the VillageIdiot's outburst refers, but I myself am quite astonished that this question does not appear to be a duplicate at all.

Answer 4

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 5

You should use scope_identity(). And I recommend to wrap insert statement and scope_identity() into transaction.