获取最后插入记录的ID

Question

我正在使用以下查询向表中插入多条记录：

INSERT INTO Table1(FirstName, LastName, EmailAddress)
    SELECT t2.FirstName, t2.LastName, t2.EmailAddress
    FROM Table2 t2

由于查询正在插入多个记录，因此我无法使用SCOPE_IDENTITY来检索PK。是否有任何方法可以获取最后插入记录的ID？

- NaveenBhat

---

不是重复问题，@nawfal - 这个问题是在询问如何检索多个插入行的ID。 - Shog9

@Shog9没错，我会删除我的评论。 - nawfal

Answer 1

SCOPE_IDENTITY()能正确返回最后一个ID。你需要将其与@@ROWCOUNT结合使用，以获得ID范围。正如其他Richard指出的那样，这仅在增量设置为1时才有效。

例如：

declare @last int, @first int
insert ...
select @last = scope_identity(), @first = scope_identity() - @@rowcount + 1

另一种方法（在SQL Server 2008中使用可保证结果）是使用OUTPUT子句来执行此操作。

declare @ids table (id int)
INSERT INTO Table1 (FirstName ,LastName ,EmailAddress)
output inserted.id into @ids

-- Get the ids
SELECT id from @Ids

现在包含了所有插入的id

- RichardTheKiwi

---

@@rowcount 很有趣。但显然它假设增量为1。我假设它假设插入没有交错（例如，不同的会话）：这可以得到保证吗？ - Richard

@Richard - 你说得很好，关于增量为1的观点。我从未使用过其他增量。我本以为插入操作是原子性和顺序性的，但如果有不同的情况，我很感兴趣了解一下。 - RichardTheKiwi

只是在测试交替插入：一个会话打开事务并进行第一个插入，然后在第二个会话中进行另一个插入。最后在第一个会话中进行另一个插入并提交。第二个会话的插入获取了ID 2，第一个会话的插入获取了ID 1和3。使用事务的并发会话插入将交错进行。我不知道单个多行插入是否可以看到这一点，但这似乎是可能的（例如，插入正在从另一个被锁定的临时表中读取）。摘要：使用OUTPUT或每次插入后使用SCOPE_IDENTITY逐个执行插入。 - Richard

（顺便说一句：使用 SQL Express 2008 R2 进行测试。） - Richard

Answer 2

SCOPE_IDENTITY 的文档还列出了 @@IDENTITY 和 IDENT_SCOPE，它们都返回一个单一的标识符，但有关范围和会话的细节不同。

解决方案：添加一行并获取其标识，然后插入下一行......

- Richard