I'm inserting a bunch of new rows into a table which is defined as follows:
CREATE TABLE [sometable]( [id] [int] IDENTITY(1,1) NOT NULL, [someval] sometype NOT NULL )
using the following insert:
insert into sometable select somefield as someval from othertable
when I've finished, I'd like to know the IDs of all the newly inserted rows.
SCOPE_IDENTITY() only returns the ID last row inserted.
How can I get all the new IDs?
One method that springs to mind would be to grab the current largest identity from sometable and the scope_identity() post-insert, and use these two values to select from sometable. For example:
declare @currentMaxId int; select @currentMaxId=MAX(id) from sometable insert into sometable select somefield as someval from othertable select * from sometable where id>@currentMaxId and id<=SCOPE_IDENTITY()
Is there a better pattern?