如何插入到表中并返回主键值?

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).

我正在执行多个查询，我需要检索主键值，以便在另一个表中用作外键(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?

有什么建议吗?

6 个解决方案

#1

insert into YourTable values (...)

get the new PK with scope_identity()

使用scope_identity()获取新的PK

select scope_identity()

#2

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

如果您正在使用SQL Server 2005或更高版本，您可以使用OUTPUT子句。

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.

从SQL Server 2008开始，您可以使用“可组合的DML”来获得更多的可能性。

#3

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

Note: This is for MS SQL 2005 and greater

注意:这是针对MS SQL 2005和更高版本的

#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.

SCOPE_IDENTITY()可能就是您想要的。它返回由它执行的代码上下文插入的最后一条记录的ID。

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.

IDENT_CURRENT('tablename')受制于并发问题。也就是说，不能保证插入和调用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:

就叫SCOPE_IDENTITY()函数:

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)

因为如果其他语句进行插入，那么选择最高值将返回错误。函数scope_identity()返回在当前上下文中创建的标识(由您的语句创建)

#6

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

您应该使用scope_identity()。我建议将insert语句和scope_identity()打包到事务中。

#1

insert into YourTable values (...)