How can I use a Table-Valued Parameter to insert multiple rows and then return their IDs?

Question

In my application, I have a large of number (100+) of rows that I need to insert into the database. Once they get inserted into the database, I need to insert their children, which have a foreign key reference back to the children.

I'm wondering if there's a way to write a stored procedure that can insert all of those rows and return their IDs back to my application?

Answer 1

You have tagged your question with table-value-parameters - you can pass one these to the stored procedure for inserting to the database.

You can use the OUTPUT clause with the INSERTED logical table to get the new ID values and return those from the stored procedure.