SQL 如何插入表并取回主键值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1360453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert into a table and get back the primary key value?
提问by leora
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?
有什么建议？
回答by Steve Kass
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”获得更多可能性。
回答by roman m
insert into YourTable values (...)
get the new PK with scope_identity()
使用 scope_identity() 获取新的 PK
select scope_identity()
回答by Linda C
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 及更高版本
回答by harpo
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') 受并发问题的影响。也就是说，不能保证在 INSERT 和对 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.
我必须承认，我不确定 VillageIdiot 的爆发指的是什么令人惊讶的原因，但我自己很惊讶这个问题似乎根本不是重复的。
回答by TheVillageIdiot
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()返回在当前上下文中创建的标识（即由您的语句）
回答by Tadas ?ukys
You should use scope_identity(). And I recommend to wrap insert statement and scope_identity() into transaction.
您应该使用 scope_identity()。我建议将 insert 语句和 scope_identity() 包装到事务中。
