获取最后插入的行ID（使用SQL语句）

Question

我想在插入新记录时获取新创建的ID。

我阅读了这篇文章：http://msdn.microsoft.com/en-us/library/ms177564.aspx，但需要创建临时表。

我想在执行INSERT语句后返回ID（假设只执行一个INSERT）。

示例：

1     Joe      Joe
2     Michael  Mike
3     Zoe      Zoe

在执行INSERT语句时，我希望返回所创建的ID，即4。

请告诉我如何使用SQL语句实现这一点，或者是否不可能？

- Snake Eyes

---

ID是身份证明主键吗？ - Thit Lwin Oo

@Thit，它是主键有什么区别呢？IDENTITY和主键是两个完全不同的概念。虽然你经常会看到它们联系在一起，但它们并不是同一件事。事实上，我今天刚刚发了关于这个误解的博客：https://sqlblog.org/blogs/aaron_bertrand/archive/2012/02/27/bad-habits-to-kick-believing-everything-you-hear-or-read.aspx - Aaron Bertrand

那么 @marc_s 已经为你提供了答案。 - Thit Lwin Oo

Answer 1

如果你的SQL Server表有一个INT IDENTITY（或BIGINT IDENTITY）类型的列，那么你可以使用以下方式获取最新插入的值：

INSERT INTO dbo.YourTable(columns....)
   VALUES(..........)

SELECT SCOPE_IDENTITY()

只要你没有插入另一行数据，这个方法就能正常工作 - 它只会返回在此范围内分配的最后一个IDENTITY值。

至少还有两种选择 - @@IDENTITY和IDENT_CURRENT - 阅读更多关于它们如何工作以及它们之间的差异（可能会给您带来意外结果）的信息，请参考Pinal Dave在这篇优秀博客文章。

- marc_s

---

请注意，SCOPE_IDENTITY()也可能产生不正确的结果（在并行情况下），请参阅http://support.microsoft.com/kb/2019779 - 修复程序在上周首次提供给2008 R2 SP1 CU5。在所有早期版本中，解决方法是将maxdop设置为1、保持一个固定的计划，该计划不使用并行处理（我没有测试过），或使用输出子句。 - Aaron Bertrand

我怀疑Pinal Dave说的scope_identity“将返回您明确创建的最后一个标识值，而不是由触发器或用户定义函数创建的任何标识”是错误的 - 我使用触发器向表中插入数据，导致插入到审核表中并且scope_identity返回null。实际上，唯一不返回null的选项是@@identity - 但它给我返回了审核表的最新ID！因此，这些方法都没有返回我原始插入的最新插入ID。SQL Server 2016。 - youcantryreachingme

@youcantryreachingme：我怀疑你的代码中有错误 - Pinal Dave通常不会错，尤其在这一点上绝对不会错..为什么不把你的观察结果写成一个问题，在这里问一下呢？ - marc_s

@marc_s - 很高兴能够看到我的错误所在。这里有一个重现场景的脚本-https://wetransfer.com/downloads/801bd0c0699b5517eef3e00970bd150220200326052640/2c8e11 - youcantryreachingme

@youcantryreachingme：好的，你的表Client没有任何IDENTITY列 - 所以 INSERT INTO Client()... 显然 不会 触发任何身份标识生成，因此SCOPE_IDENTITY()将是NULL:.... - marc_s

@marc_s感谢您提供这样一个充满感情的解释！哇！我没有想到会有这么强烈的语气！我忘了一个主键——即唯一的标识符——可能与表的身份列相同（尽管我想过非int和组合PK）。对于其他被困扰的人，这里有一个好的解释：https://dev59.com/C2855IYBdhLWcg3whExL。 - youcantryreachingme

Answer 2

假设有一张简单的表：

CREATE TABLE dbo.foo(ID INT IDENTITY(1,1), name SYSNAME);

我们可以使用表变量来捕获 IDENTITY 值以供进一步使用。

DECLARE @IDs TABLE(ID INT);

-- minor change to INSERT statement; add an OUTPUT clause:
INSERT dbo.foo(name) 
  OUTPUT inserted.ID INTO @IDs(ID)
SELECT N'Fred'
UNION ALL
SELECT N'Bob';

SELECT ID FROM @IDs;

这种方法的好处是（a）它可以处理多行插入（SCOPE_IDENTITY() 只返回最后一个值），（b）它避免了并行性bug，该bug可能导致错误结果，但目前仅在SQL Server 2008 R2 SP1 CU5中修复。

- Aaron Bertrand

---

只有当你认为编程不仅仅是通过创造模糊的错误来确保工作安全时，正确性才是重要的。 </讽刺> - Eliot Gillum

我希望有一种简写语法，可以在只有一个结果时直接将值放入变量中。如果存在多行，则可以抛出错误（如在某些上下文中的子查询）或仅使用最后一行的值（类似于基于多个连接行的输入更新单个行的更新语句的行为）。 - BVernon

在一个事务中，这种情况仍然存在吗？ - crush

@BVernon 当你知道只会有一个值时，已经有了简写语法：SELECT @ID = SCOPE_IDENTITY(); - Aaron Bertrand

Answer 3

您可以使用：

SELECT IDENT_CURRENT('tablename')

访问特定表的最新标识符。

例如，考虑以下代码：

INSERT INTO dbo.MyTable(columns....) VALUES(..........)

INSERT INTO dbo.YourTable(columns....) VALUES(..........)

SELECT IDENT_CURRENT('MyTable')

SELECT IDENT_CURRENT('YourTable')

这将产生相应表的正确值。

它返回表中生成的最后一个IDENTITY值，无论创建该值的连接是什么，以及生成该值的语句的作用域是什么。

IDENT_CURRENT不受范围和会话限制；它仅限于指定的表。IDENT_CURRENT在任何会话和任何范围内返回为特定表生成的标识值。

- MaxRecursion

---

根据您的陈述：“不考虑创造价值的连接” - 这听起来毫无意义 - 如果另一个连接在我之后插入了一行，我会得到他的编号 - 然后我会尝试更新我插入的行，但实际上会更新他的。对吗？ - Traderhut Games

你使用了‘和’字符，这不是有效的SQL语法。请改用有效的SQL语法 ' 字符。我已经修复了这个问题。 - Ruudjah

这个帖子为什么有49个赞我不理解。IDENT_CURRENT无法可靠地确定我插入的最后一行，但它是一个很好的方法，也许可以获取我或其他任何人插入的最后一行。请参阅最后一次：不，你不能信任IDENT_CURRENT()。 - Aaron Bertrand