I have a complex, flexable and very hand database backing a site I am working on. The problem is that I have hit the edge of the curve a little here and tend to find myself creating SQL via script that is "too complex" to be processed.

The solution seems to be to create a gaint table that caches the most used data in some massive flat file tables where everything is (hopefully) set to indexed (Duplicates OK).

I have created a sample table with all the fields I think we will need and I have removed no duplicates and auto inciments from the lot.

Both will be Access 2K DB and I will need to run an ASP script (Using VBScript) to pull the data as gently as possible from the over worked DB and insert it into the cache DB which the search functions will be switched too in due course

The Cache DB will have somewhere in the order of 10,00,000 or more records going into it probably on a daily or twice daily basis.

At some future point there will be an upgrade to MySQL or SQL Server but I have to justify the expence and time it will require re-writting embedded SQL etc etc.

Now my question - is there a nice simple and easy way to dump my returned record set into another DB via a make table or append or something. What I don't want to do is set the script to itterate through all the records writting them one at a time... the sites hosts might feel non-to kindly to that level of useage on top of the users demands.