CODEWITHSUNDEEP
sqlsql-server
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34150

Get List of all last inserted IDs to a table

When I want to get last inserted ID to a table I use it like this:

insert into table1(col1,col2,col3) 
      values(val1,val2,val3)

declare @last_id int = scope_identity()

Now I have stored procedure that gets a value inserts multiple items:

insert into table1(col1,col2,col3)
      select @val1,@val2,val3 from table2 where value=@value  //@value is a single value that is passed to procedure as argument

now how can I get the list of IDs of these multiple rows that are added to table1?

Upvotes: 1

Views: 1573

Answers (2)

GriGrim
GriGrim

Reputation: 2921

declare @inserted table (Id int)

insert into table1(col1,col2,col3)
output inserted.Id into @inserted (Id)
select @val1,@val2,val3
from table2
where value=@value

select * from @inserted

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

The SCOPE_IDENTITY() gets you the last generated Identity Value in the identity column. to get all the newly inserted values you will need to make use of OUTPUT clause along with a table variable. See below how you can do that.

DECLARE @New_IDs TABLE(IDs INT)


insert into table1(col1,col2,col3)
OUTPUT inserted.Id_column INTO @New_IDs
select @val1,@val2,val3 
from table2 
where value=@value

SELECT * FROM @New_IDs

Upvotes: 3

Related Questions