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?

This has the smell of a bad plan. Why do it this way, you think it will be faster? Just do them one by one -- less problems IMHO http://stackoverflow.com/questions/312003/what-is-the-most-ridiculous-pessimization-youve-seen — Hogan, Nov 05 '11 at 19:29
@Hogan - This is actually a very good optimization. A single database call will be much much much more performant than 100. Going over the network is _very_ expensive. — Oded, Nov 05 '11 at 19:34
@Oded - He still has to do a call for each child; wouldn't it be better to pass the parent and child to a SP and do 100 calls and not 101? This plan would also reduce network traffic -- there is no need to pass the IDs back to the logic tier. — Hogan, Nov 05 '11 at 20:08
@Hogan - You are assuming wrong. It could end up as 2 calls, with the second call requesting all the children in one go. — Oded, Nov 05 '11 at 20:09
@Oded - In that case why not pass all the children with the parents in the first call and do 1 call and not 2? Like I said, bad smell. — Hogan, Nov 05 '11 at 20:10
One thing to mention is that there are more children than there are parents, so it's not a one-to-one mapping. So if I passed them together to the sproc, I'd still need some way of mapping the children to their parents. — Jeffrey Lott, Nov 06 '11 at 17:47

score 3 · Accepted Answer · answered Nov 05 '11 at 19:37

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.

score 3 · Answer 2 · answered Nov 05 '11 at 19:56

Here's an example of using the output clause, like Oded suggested. First, some setup code:

if exists (select * from tempdb.sys.tables where name like '#tmp%')
    drop table #tmp
create table #tmp (id int identity, FirstName varchar(50), LastName varchar(50))

if exists (select * from sys.procedures where name = 'TestProcedure')
    drop procedure TestProcedure
if exists (select * from sys.types where name = 'TestTableType')
    drop type TestTableType

create type TestTableType as table (FirstName varchar(50), LastName varchar(50))
go

Now we can create the stored procedure:

create procedure dbo.TestProcedure
    @List TestTableType readonly
as
insert  #tmp
        (FirstName, LastName)
output  inserted.*
select  FirstName
,       LastName
from    @List l
go

Note the output clause, which tells SQL Server to return the inserted rows back to the client, including the value of the identity column. Now that the code is all set up, we can test it:

declare @List TestTableType
insert @List values ('Rick','Cain'),
    ('Herman', 'Gingrich'),
    ('Newt', 'Paul'),
    ('Ron', 'Perry')

exec  dbo.TestProcedure @List

select * from #tmp

You'll see that the values returned by the procedure match the values in #tmp exactly.

score 0 · Answer 3 · edited Oct 21 '12 at 10:18

I'm still researching how to do this, but it seems to me if you want to insert a parent table and a child table, it could be done with one sql call by passing both tables in as table value parameters. If you have some artificial identity (relationship) which currently links the two tables like a row number, but is not necesarily stored in either DB table, then when the parent is inserted and gets its identity value, it could be joined to the child table using the unique relationship and the child's foreign keys could be updated. The child could then be inserted.

The output is interesting, but I don't actually need the output, it simply has to be used within the sproc, so i have to do more research to see if the output can be used by, for example, outputing it to a temp table.

Answering my own question the syntax for Output is:

[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]

so it can be used to create a temp table that is joined with a child.

You should ask a new question and answer it self – j0k Oct 21 '12 at 10:19 — j0k, Oct 21 '12 at 10:19

score 0 · Answer 4 · answered Nov 06 '11 at 23:28

0

I would do something similar to what Andomar suggests, but using the "INSERT-EXEC" technique described here :

http://www.sommarskog.se/tableparam.html

In the link, search for : INSERT-EXEC

answered Nov 06 '11 at 23:28

Moe Sisko

11,665
8
50
80

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

4 Answers4

Linked