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?
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', 'john@somewhere.com'),
('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