SQL 如何从 SQLServer SELECT 语句返回新的 IDENTITY 列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/976593/
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 return a new IDENTITY column value from an SQLServer SELECT statement?
提问by skiphoppy
I'm inserting into an SQLServer table with an autoincrementing key field. (I believe this is called an IDENTITY column in SQLServer.)
我正在插入一个带有自动递增键字段的 SQLServer 表。（我相信这在 SQLServer 中称为 IDENTITY 列。）
In Oracle, I can use the RETURNING keyword to give my INSERT statement a results set like a SELECT query that will return the generated value:
在 Oracle 中，我可以使用 RETURNING 关键字为我的 INSERT 语句提供一个结果集，如将返回生成值的 SELECT 查询：
INSERT INTO table
(foreign_key1, value)
VALUES
(9, 'text')
RETURNING key_field INTO :var;
How do I accomplish this in SQLServer?
如何在 SQLServer 中完成此操作？
Bonus: Okay, nice answers so far, but how do I put it into a single statement, if possible? :)
奖励：好的，到目前为止的答案都很好，但是如果可能的话，我如何将其放入单个语句中？:)
回答by richardtallent
In general, it can't be done in a single statement.
一般来说，它不能在单个语句中完成。
But the SELECT SCOPE_IDENTITY() can (and should) be placed directly after the INSERT statement, so it's all done in the same database call.
但是 SELECT SCOPE_IDENTITY() 可以（也应该）直接放在 INSERT 语句之后，所以这一切都在同一个数据库调用中完成。
Example:
例子：
mydb.ExecuteSql("INSERT INTO table(foreign_key1, value) VALUES(9, 'text'); SELECT SCOPE_IDENTITY();");
You can use OUTPUT, but it has some limitations you should be aware of:
您可以使用 OUTPUT，但它有一些您应该注意的限制：
回答by gbn
SELECT SCOPE_IDENTITY()
Edit: Having a play...
编辑：有戏...
If only the OUTPUTclause supported local variables.
如果只有OUTPUT子句支持局部变量。
Anyway, to get a range of IDs rather than a singleton
无论如何，要获得一系列 ID 而不是单例
DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))
INSERT @Mytable (valuecol)
OUTPUT Inserted.keycol
SELECT 'harry'
UNION ALL
SELECT 'dick'
UNION ALL
SELECT 'tom'
Edit 2: In one call. I've never had occasion to use this construct.
编辑 2：在一次通话中。我从来没有机会使用这个结构。
DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))
INSERT @Mytable (valuecol)
OUTPUT Inserted.keycol
VALUES('foobar')
回答by Tom H
In addition to @@IDENTITY, you should also look into SCOPE_IDENTITY() and IDENT_CURRENT(). You most likely want SCOPE_IDENTITY(). @@IDENTITY has a problem in that it might return an identity value created in a trigger on the actual table that you're trying to track.
除了@@IDENTITY，您还应该查看 SCOPE_IDENTITY() 和 IDENT_CURRENT()。您很可能需要 SCOPE_IDENTITY()。@@IDENTITY 有一个问题，它可能会返回在您尝试跟踪的实际表上的触发器中创建的标识值。
Also, these are single-value functions. I don't know how the Oracle RETURNING keyword works.
此外，这些是单值函数。我不知道 Oracle RETURNING 关键字是如何工作的。
回答by Ken Burkhardt
SCOPE_IDENTITY
SCOPE_IDENTITY
回答by Rob Garrison
It depends on your calling context.
这取决于您的调用上下文。
If you're calling this from client code, you can use OUTPUT and then read the value returned.
如果您从客户端代码调用它，您可以使用 OUTPUT 然后读取返回的值。
DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))
INSERT INTO @t (ColStr)
OUTPUT Inserted.ColID
VALUES ('Hello World')
Result:
结果：
ColID
-----------
1
If you're wrapping this in a stored procedure, using OUTPUT is more work. There, you'll want to use SCOPE_IDENTITY(), but you can't do it in a single statement. Sure, you can put multiple statements on a single line with a ';' separator, but that's not a single statement.
如果您将其包装在存储过程中，则使用 OUTPUT 会更有效。在那里，您会想要使用 SCOPE_IDENTITY()，但您不能在单个语句中完成。当然，你可以用';'将多个语句放在一行中 分隔符，但这不是一个单一的声明。
DECLARE @idValue int
DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))
INSERT INTO @t (ColStr) VALUES ('Hello World')
SELECT @idValue = SCOPE_IDENTITY()
Result: @idValue variable contains identity value. Use an OUTPUT parameter to return the value.
结果：@idValue 变量包含标识值。使用 OUTPUT 参数返回值。
回答by Bhaskar
INSERT INTO table(foreign_key1, value)VALUES(9, 'text');SELECT @@IDENTITY;
INSERT INTO table(foreign_key1, value)VALUES(9, 'text');SELECT @@IDENTITY;
回答by Cade Roux
You can use OUTPUT INTO, which has the additional benefits of being able to capture multiple identities inserted.
您可以使用OUTPUT INTO，它具有能够捕获插入的多个身份的额外好处。
