CODEWITHSUNDEEP
sqlsql-servert-sql
leora
leora

Reputation: 196469

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

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?

Upvotes: 26

Views: 56225

Answers (6)

Linda C
Linda C

Reputation: 131

INSERT INTO YourTable (1, 2, etc.)
OUTPUT inserted.yourIDcolumn
VALUES (value1, value2, value...)

Note: This is for MS SQL 2005 and greater

Upvotes: 12

Tadas Šukys
Tadas Šukys

Reputation: 4220

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

Upvotes: 2

Steve Kass
Steve Kass

Reputation: 7184

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.

Upvotes: 43

harpo
harpo

Reputation: 43168

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.

Upvotes: 5

TheVillageIdiot
TheVillageIdiot

Reputation: 40497

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)

Upvotes: 3

roman m
roman m

Reputation: 26521

insert into YourTable values (...)

get the new PK with scope_identity()

select scope_identity()

Upvotes: 33

Related Questions