6

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NaveenBhat
  • 3,248
  • 4
  • 35
  • 48

2 Answers2

13

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

Richard
  • 106,783
  • 21
  • 203
  • 265
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 2
    The `@@rowcount` is interesting. But clearly it assumes an increment of 1. And I assume it assumes no interleaving (eg. different sessions) of inserts is possible: can this be guaranteed? – Richard Mar 16 '11 at 09:58
  • @Richard - good point about increment of 1. I have never used any other increment. I would have thought the inserts would be atomic and sequential, but would be interested to know if it is otherwise – RichardTheKiwi Mar 16 '11 at 10:00
  • 1
    Just testing interleaving inserts: one session opens transaction and does 1st insert. Then in session two do another insert. Finally in first session do another insert and commit. The insert from the second session gets id 2, and the first session ids 1 and 3. With concurrent sessions using transactions inserts will interleave. I don't know whether a single multi-tow insert could see this, but it is looking possible (eg. insert is reading from another table which is temporarily blocked by a lock). *Summary:* use `OUTPUT` or perform inserts one at a time with `SCOPE_IDENTITY` after each insert. – Richard Mar 17 '11 at 14:48
  • (BTW: testing with SQL Express 2008 R2.) – Richard Mar 17 '11 at 14:49
0

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….

Richard
  • 106,783
  • 21
  • 203
  • 265