October 5, 2001 at 2:02 am
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.....
thanks
October 5, 2001 at 7:38 am
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.
Andy
October 5, 2001 at 2:22 pm
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
as
declare @pk int
exec @pk = spInsParent @p1
/* error check */
exec spInsChild @pk, @p2
return
create proc spInsParent
@p int
as
insert into parenttable @p1
return @@scope_identity /* or other pk value */
create proc spInsChild
as
insert into ChildTable @pk, @C
return
Steve Jones
October 5, 2001 at 5:18 pm
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.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply