1

I've got a call to a stored procedure, that is basically an INSERT stored procedure. It inserts into Table A, then into Table B with the identity from Table A.

Now, i need to call this stored procedure N amount of times from my application code.

Is there any way i can batch this? At the moment it's doing N round trips to the DB, i would like it to be one.

The only approach i can think of is to pass a the entire list of items across the wire, via an User Defined Table Type.

But the problem with this approach is that i will need a CURSOR in the sproc to loop through each item in order to do the insert (because of the identity field).

Basically, can we batch DbCommand.ExecuteNonQuery() with EF 4.2?

Or can we do it with something like Dapper?

RPM1984
  • 72,246
  • 58
  • 225
  • 350

1 Answers1

0

You can keep it like that and in the stored procedure just do a MERGE between your target table and the table parameter. Because you are always coming with new records, the MERGE will enter only on the INSERT branch.

In this case, using MERGE like this is an easy way of doing batch inserts without a cursor.

Also, another way which also avoids the use of a cursor is to use a INSERT from SELECT statement in the SP.

Marcel N.
  • 13,726
  • 5
  • 47
  • 72
  • i cant do this, because of the IDENTITY field required from each insert into table A. the stored proc needs to do 2 inserts, table A (which gets the identity), then table b (with the new identity). i could insert a bunch of records into table a with a merge (or insert from select) no problem, but how do i get the records into table b with the identity? i cant, unless i join on another field (which isn't unique, and hence that wont work either) – RPM1984 Jun 27 '12 at 05:45
  • @RPM1984: What about using the `OUTPUT` statement for the `MERGE` in table A and then use it for inserting in table B? `OUTPUT` can give you the identity column + any other column you may want in a temp table or in a table variable and you can use it to insert in B. This applies if I'm not missing anything about the relations between A and B. – Marcel N. Jun 27 '12 at 05:55
  • Docs and samples for `OUTPUT` here: http://msdn.microsoft.com/en-us/library/ms177564.aspx – Marcel N. Jun 27 '12 at 05:55
  • ahh now your onto something...will try this out and get back to you. – RPM1984 Jun 27 '12 at 08:54