Need help regd. Multiple inserts in a SP

  • hi everyone,

    I have bunch of datas to be inserted into a table and its child table using ADO and SP. i am planning to insert or update the datas as follows...

    1. first saving the datas of master table using a SP and saving other datas in the child table using a different stored procedure and ASP.

    can i use a single SP to insert all datas in both the tables.? but i fear it may lead to server timeout.....

    Please clarify this problem.....


  • I think you mean something like an order and the associated order details? Yes, you can do it all in once procedure, though its not pretty. You have to pass everything as parameters, so you end up with something like this:

    sp_addorderanddetail @CustomerID, @ShipDate, @Item1SKU,@Item1Qty, @Item2SKU, @Item2Qty, etc etc

    Then in the proc, you first do the insert for the order (to get its primarykey), then do inserts of the details. You'd have to be adding a LOT of child records to get a timeout, inserts are typically fast and lightweight. What you're proposing is a good solution since it minimizes the round trips between client and server.


  • Alternatively you can use multiple inserts in a single transaction, but I'd avoid this. If the client fails between the items, your transaction may die.

    Personally I like one (not pretty) sproc. Usually I have a sproc for each table and a master that can insert using these.

    create proc masterinsert

    @p1, @p2


    declare @pk int

    exec @pk = spInsParent @p1

    /* error check */

    exec spInsChild @pk, @p2


    create proc spInsParent

    @p int


    insert into parenttable @p1

    return @@scope_identity /* or other pk value */

    create proc spInsChild

    @pk int, @C int


    insert into ChildTable @pk, @C


    Steve Jones

  • One other comment - this is one of those situations where using a uniqueidentifier for your primarykey is awesome, since you can generate the key on the client, not need to get a return value from the parent record to use for the child records.


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply