获取最后插入记录的ID [英] Get the ID of last inserted records

问题描述

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

I'm inserting multiple records to a table using the below query:

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

由于查询插入了多条记录，我无法使用 SCOPE_IDENTITY 来检索 PK.有没有什么方法可以获取最后插入记录的ID?

Since the query is inserting multiple records, I can't use SCOPE_IDENTITY to retrieve PK. Is there any method to get the ID's of last inserted records?

推荐答案

SCOPE_IDENTITY() 将正确地为您提供 LAST ID.您需要的是将它与@@Rowcount 结合起来为您提供 ID 的范围.正如另一位理查德指出的，这仅在您的增量设置为 1 时才有效

SCOPE_IDENTITY() will correctly give you the LAST ID. What you need is to combine it with @@Rowcount to give you the range of IDs. As the other Richard points out, this only works if your increment is set to 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

The table now contains all the inserted ids

这篇关于获取最后插入记录的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！