Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Aiby
Yak Posting Veteran
71 Posts |
 Posted - 2004-11-06 : 15:36:02
|
| Till today I have nt got the answer for this issue How I can INSERT using a single SP and Input Data Master table and Multiple Rows to the related Child tables -– ( with out calling a stored procedure in a loop from the application ) –A test table Structure is : Master Table Chile Table -------------- ------------ [ Invoice ] [Invoice_Item] InvoiceNo InvoinceNo SupplierID ProductID InvoiveDate Qty NetAmount RateData To Master Table >>> InvoinceNo = (Auto Increment ) 12121 05/25/04 5050.00to Child Table>>>>> (Last incremented No) 1001 10 120 (Last incremented No) 1012 10 35 (Last incremented No) 2155 2 250Can I input these data to a single stored procedute to update Master Table and Child table? (avoiding multiple calls of stored procedure to update child tables from Application)( It should be better if you can list me few sample code)Thank you.Aiby Mohan DasAnalyst ProgrammerDecibel Infotech P.Ltd.Kerala, IndiaEmail: Aiby@hotmail.com |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-06 : 15:45:58
|
from where are you calling the sp? if you're using ado.net then yes you can.just use dataset with two tables and set a parent row to each child table row.i don't think it can be done in ado (not .net). but i can be mistaken.by using just one sproc how would you pass paramters for each child row?? that's impractical.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Aiby
Yak Posting Veteran
71 Posts |
Posted - 2004-11-06 : 15:58:54
|
| I would like to call from the Visual Basic Application.Aiby |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-06 : 16:18:30
|
| The problem is getting the data into the SP.If you have a limit on the number of invoice items then define parameters for each one - leave as nulls the ones that aren't to be filled in.If you don't want a limit (or a very large limit) then there are a number of options.Pass them as csv strings and parse the string in the sp to get the individual items.Populate a temp table from the app and access it in the sp.Create a xml (or other file) from the app and load it in the sp.Often the solution is to limit the number or invoice items passed (to say 10) and if you need more then call the sp multipl times.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-06 : 17:03:02
|
| I vote for the xml option |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-06 : 17:06:09
|
| me too...Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-07 : 03:14:29
|
| and me ... we've recently started doing this where we need an SProc to insert "a number of rows" and found it much easier than all the CSV stuff we used to do before. And multiple table stuff (like Master / Child) is a breeze too.Kristen |
|
|
|
Aiby
Yak Posting Veteran
71 Posts |
Posted - 2004-11-07 : 04:58:54
|
Suggest me with code please.. if possible..!quote:
Originally posted by Kristen
and me ... we've recently started doing this where we need an SProc to insert "a number of rows" and found it much easier than all the CSV stuff we used to do before. And multiple table stuff (like Master / Child) is a breeze too.Kristen
Aiby Mohan DasAnalyst ProgrammerDecibel Infotech P.Ltd.Kerala, IndiaEmail: Aiby@hotmail.com |
|
|
|
Aiby
Yak Posting Veteran
71 Posts |
Posted - 2004-11-07 : 05:01:04
|
| People who voted for XML! Requested to post few sample code, how can integrate those in VB applications.. Thank you..Aiby :P |
|
|
|
|
|
|
How Can I Update Master Tbl & Ch: Tbls using singl