Get the ID of last inserted records [duplicate]

This question already has answers here: How to get last inserted id? (16 answers) Closed 9 years ago.

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

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() 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

For example:

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

Another way (use this in SQL Server 2008 for guaranteed results) to do this is to use the OUTPUT clause

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

-- Get the ids
SELECT id from @Ids

The table now contains all the inserted ids

---

The documentation for SCOPE_IDENTITY also lists @@IDENTITY and IDENT_SCOPE all of which—with different details about scope and sessions—return a single identifier.

Solution: Add one row and get its identity, then inset the next….