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 |
|
chadbryant5
Starting Member
32 Posts |
 Posted - 2006-11-27 : 14:18:51
|
| Hi,We have a feature in our asp.net web application that we've added for a customer. They wish to have an Access database of their records that they can generate at any time. What we've done is setup a blank Access db with all the tables. We have a SQL 2000 db that drives our web application. The user goes to a web form and enters some input such as date range, etc and that corresponds to a list of records that are within that date range etc. From that resultset we need to build the Access database by making a temp copy of it and then using the resultset from the SQL Server query to insert rows into the Access db tables and then we let the user download the newly built Access db.Right now this is taking forever and the users are complaining so I'm trying to figure out the fastest way to handle this and just looking for ideas.There could be as many as 20,000 rows for the main table and then for each row in the main table there could be 10 or so child rows in the 8 or so child tables. So imagine something like an Orders table and for each order (of about 20,000 potentially) you need to insert child rows into the child tables of the orders table. There are 8 child tables and for a particular order you might have 1 or 0 maybe rows inserted into a table and another could have as many as 10 rows inserted into that particular child table for that particular order.Right now we have an ADO.NEt based data access layer class that gets back a .NET DataSet with DataTables representing the results from the various tables. I am going through each row in the Orders main table and then for each row in there going through the child tables and inserting the rows from that table into the Access table. It seems like this could be optimized somehow so that things are done in more of a batch fashion, but I'm not sure how and I'm not that familiar with Access or OLE Db providers. We typically program against SQL Server and we use the SQL client ADO.NET capabilities rather than the generic OLE Db access methods.Any help with this would be greatly appreciated!!Chad |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:22:15
|
| Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75465 |
 |
|
|
|
|
|
batch copying records from SQL Server to Access