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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-12-30 : 17:52:29
|
| Hi,I need to allow my users to make multiple inserts to my SQL db via a single ASP page (I have a dual primary key so multiple inserts are allowed.)For example, one entity may buy several products and I want to be able to offer one ASP form which passes Joe Schmoe's ID and then a list of the things he's bought, one unique record per purchase.I'm passing parameters to SPROCs using ADO as recommended by several folks in this forum and wondered if anyone had some advice on how I could extend this technique to accomplish the above.thx |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-12-30 : 17:56:15
|
| if your using SQL Server 2000, then you can pass in an XML document that then parses out into a table that you can then insert the data... i've done this for mass importing invoice data and it is very easy, read up on sp_xml_preparedocument ... i think thats it ... if you don't have 2k, then just make one procedure that creates an order and one that adds an item to it, then call that procedure over and over again ... |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2002-12-31 : 08:24:14
|
| Hi:I dont think that this is the most efficient way, but while we were running SQL 7, we had to make do with passing such information in delimited string. By combining various delimiters, you can pass multiple columns of information to the stored proc, like:(ProductID, Qty, Discount)1020,3,0;1992,4,10;802,5,12That was the purchase info for 3 products, passed a single varchar parameter to the sproc. Inside the proc, we used string functions like CHARINDEX to split it up into various rows and columns.OS |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-12-31 : 09:28:12
|
| I'm really against array like use since SQL server does not natively understand the concept of an array. Delimited string can just cause problems in the future. A single insert per item is not all that bad, it allows each item to be processed without using a SQL server loop, which is not highly efficient (compared to compiled loops) |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-12-31 : 09:32:53
|
| The more I read the comments here, the more I think I'm going to force a single insert per item on the ASP page. It's easier to manage that way in the long run and users are not likely to complain anyway.thx |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-31 : 10:02:42
|
| steelkit,All you need to do is collect all the id's of the items and the id of the person that bought them.Next use the command object to pass these to a stored procedure as personid, (csv of itemid's).Then use the following technique to create a table.http://www.sqlteam.com/item.asp?ItemID=2652Lastly do an insert from INSERT INTO tableA (personid, itemid)SELECT @personid, itemid from mytable |
|
|
|
|
|
|
|
|
multiple record INSERTS via SPROC