Get multiple scope_identity while inserting data with table-valued parameter

Question

I am inserting multiple rows into Table1 using table-valued parameter. Now I want to insert Table1's scope identity to Table2 with some values. How do I achieve that?

Answer 1

You can use the Output Clause clause for that, lets look at a sample

suppose your Table1 looks like this

Table1(Table1_ID int identity, Name varchar(100), Email varchar(100), ...)

Now lets insert and catch all new ID's and Names :

declare @OutputTbl table (ID INT, Name varchar(100))

insert into Table1(Name, Email)
output inserted.Table1_ID, inserted.Name into @OutputTbl(ID, Name)
VALUES ('john doe', '[email protected]'), 
       ('Anna', 'Anna@1com')

select * from @OutputTbl

the result in @OutputTbl will be

ID  Name    
--  --------    
18  john doe    
19  Anna    

Now you can off course insert all rows from @OutputTbl into another table if you so desire

insert into Table2 (Table1_ID, Name) 
select ID, Name
from   @OutputTbl