DB to DB chache table with minimum server strain

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.

I've seen a code for copy table (or sth like insert table into table) that I found on a google search but I got the impression it was only for sql server, and the idea was that the two tables had identical structures already. Of course in the end, I don't know if it did anything different than line by line copy just like you could code asp to do, but at some point the data has to be phsically moved anyway so the transfer time is going to have to happen however you do it.

I just did a google on this and found this. i think it is the one i used before and it worked:
INSERT INTO TABLE2 SELECT * FROM TABLE1
again, it requires that they are fully compatible structures (I think if both tables had identity column it may crash)
the alternative is selected columns:
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1

I don't see any reason why it wouldn't work in Access, but it may not.
anyway, please do make the leap to Sql Server. It took me months to convince my boss to make the switch. but the first time our Access crashed and we lost about 3 days' work, it was an easy sell.

:blush:
geez. sorry. i just realized you are working with 2 diff dbs.
no, when i moved our system over to SS from access, I just did the asp row by row, table by table transfer.

i guess i don't have anything to add here.
sorry.

Quote:

---

Originally Posted by wengang

anyway, please do make the leap to Sql Server.

---

I have one SQLServer DB available for my use if I want it. The only thing that has held me back is that SQL Server is way more expensive than any of the other options and I had toyed with the idea of going ASP + MySQL on IIS as a cheaper and comparible solution.

It's fear of getting locked into expensive hosting that has held me back.

I have hit the outside edge of what an Access DB can do.

If I were to go SQL Server what would be my best option given that it is all remotly hosted without any "root" style access (currently on a shared account)?

(Does SQL Sever have the MS equiv of PHPMyAdmin?)

Well, how expensive is SQL Server? If you shop around you should be able to find a reasonably priced one. It isn't necessary to have your sqlserver database on your web host machine either. I remember earlier this year I was looking at a company in the U.S. who hosts SS databases, can't remember it now.
Google search: "sql server hosting" cheap and you'll see a few places to check.
So you can have your site anywhere and set up your SS with one of those. Shouldn't be more than a few dollars a month.

As for as control, if you install SQL Server on your PC, you can use the Enterprise Manager to design tables, stored procedures, user-defined functions, use Query Analyzer to develop your queries. That's as much control panel as I've ever needed.

Quote:

---

Originally Posted by wengang

As for as control, if you install SQL Server on your PC, you can use the Enterprise Manager to design tables, stored procedures, user-defined functions, use Query Analyzer to develop your queries. That's as much control panel as I've ever needed.

---

How expensive would a copy of that be?

:blush:
don't know. I downloaded mine.