How do I insert into a table and get back the prim

问题:

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?

回答1:

insert into YourTable values (...)

get the new PK with scope_identity()

select scope_identity()

回答2:

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.

回答3:

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

Note: This is for MS SQL 2005 and greater

回答4:

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.

回答5:

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)

回答6:

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