| Author |
Topic |
|
sujeethbala2110
Starting Member
29 Posts |
 Posted - 2006-09-12 : 00:44:42
|
| i have 10 to 15 records in my recordset object. i have to insert all these records in one call to the server instead of calling 15 times.i am new to sqlserver. please give me a solution with code. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-12 : 00:51:24
|
| IIRC, the recordset object will raise an INSERT statement on the database for each record.To get around this you could use a UNION statement, but it would mean manually creating the SQL.INSERT INTO yourTable SELECT record1 UNION SELECT record 2 etc. |
 |
|
|
sujeethbala2110
Starting Member
29 Posts |
Posted - 2006-09-12 : 02:51:08
|
| thanks for the reply. can u explain it little more with example. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-12 : 17:44:12
|
| Look up UNION statements on Books Online. My example should be enough to be going on with, given a bit of research. I would also point out that this should be a last resort - the code to accomplish this (parsing out data from a recordset to create a big UNION SQL statement) would be messy and prone to bugs. I've never seen the need to hit the database with >1 record at any one time - what's different about your requirements? |
|
|
|
sujeethbala2110
Starting Member
29 Posts |
Posted - 2006-09-13 : 00:16:02
|
| i have 2 tables. purchase_hdr and purchase_Detailsin vb i have a screen for this. for master record i have given textboxes and for child records i am using grid. the recordset which i am binding to the grid is not selecting from any database table. using recordset's fields.append methad a am creating it. then user will go on entering products purchased into the recordset. after adding 10 to 15 products they will finally click save button. here i insert a record into the purchase_hdr table using the connection object's execute methad. then i need to add the 10 child records to purchase_details. for this i am using a while loop. here also am using connection objects execute statement. this will make 10 calls to the server right ?. is it the better way of doing this or is there any other way of dong this. ? |
|
|
|
sujeethbala2110
Starting Member
29 Posts |
Posted - 2006-09-13 : 00:56:45
|
| hi i am using union all. its working fine. thanks, is there any adverse effect on my query if i use in this way ? |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-13 : 00:58:13
|
| That's the right way of doing it. You can make it as complicated as you want, but I would recommend you concentrate on doing the job properly (ie according to accepted standards) before you start worrying about stuff that probably won't give you any benefit in the long term. As I said above, it is technically possible to do all this in one hit to the database, but the code involved would be a nightmare to debug and maintain. If you stick to the KISS principle (Keep It Simple, Stupid) you can't go too far wrong.HTH,Tim |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-13 : 01:09:31
|
| You're up early Peter...... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-13 : 01:22:29
|
| Not here in Sweden. Breakfast already!Peter LarssonHelsingborg, Sweden |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-13 : 01:27:01
|
| I always browse forums over my bacon & eggs too..... :-) |
|
|
|
|
bulk insert using vb/sql server2000