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?
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.
into clause which makes this a whole lot more clunky than scope_identity for single records
insert into YourTable values (...)
get the new PK with scope_identity()
select scope_identity()
SCOPE_IDENTITY() work when used in JDBC batches?INSERT INTO YourTable (1, 2, etc.)
OUTPUT inserted.yourIDcolumn
VALUES (value1, value2, value...)
Note: This is for MS SQL 2005 and greater
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.
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)
You should use scope_identity(). And I recommend to wrap insert statement and scope_identity() into transaction.
