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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2009-07-21 : 05:32:46
|
| Hey Guys,Well at least I think its advanced :)I have an "order form" where clients can both submit an order, and create an account on the same form submission. Basically they enter their email, and if they don't have an account already, it creates an account for them in the "clients" table, and then puts their order in the "order" table.Each "order" has specific properties that we insert into the "orders" table. Each order consistens of 1 - xxx "ordered items" which we insert into the "ordered items" table.So a new account that placed an order for 3 items would have 1 row put into the clients table, 1 into the orders table, and 3 into the ordered items table.I have no problem doing this with some back and forth from asp.net, just looping thru each item and inserting it.I am wondering if there is as better way to do this, with 1 insert. Since I don't know the # of "ordered_items" being passed, this would have to be an array or something. I'm not sure if I am making this harder than it needs to be, I don't believe there is anything necessarily hugely wrong with the multiple insert way (except it could fail in the middle of the transactions), just seeing what other options their might be.Any suggestions grealy appreciated !Thanks again,mike123 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-07-21 : 06:50:48
|
| what's the maximum number of order items that you are likely to get?I could see a case for it if you were writing hundreds of lines sequentially but if it's 3 / 4 then.... maybe not worth the rework.Are you ever experiencing time-outs / blocking with your current method?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-07-21 : 08:12:28
|
| Hi Charlie,I think 90% would be between 1-10 items, up to a maximum of say 20 items. I haven't put the web application live, so don't have any problems yet. The database and load on the database are very small, so I am not expecting any timeouts or blocking .. Just trying to see *IF* there is a better way and what way that might be... also am interested ot know incase I do something like this in high performance environment where blocking might be more of an issue.thanks again!mike |
|
|
|
|
|
|
|
|
advanced inserting question