The best way to transfer the data, as has been mentioned, it to dump to a flat file, then suck into the target database. Most database engines have a method for loading comma or otherwise delimited files.
One caution: Since the source database has already checked the referential integrity and constraints (if any) of the tables, do not enable them in the target database until you have finished loading. This is not only safer, it is also much faster. Chikeobi O. Njaka Technical Operations Director Conditional Access Systems Engineering NDS Americas, Inc Phone: +1-949-725-2582 Cell: +1-949-633-6120 Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> http://www.nds.com -----Original Message----- From: PARLEY,JON (HP-MountainView,ex1) [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 2:30 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: Transferring from Access to MySQL Ian, I'd like to add that you may want to control the size of the files you dump (probably by limiting the rowset you dump to each file), making sure you don't create a file that can't be properly accessed by the OS. _/ Thunder Jon Parley _/ Software Engineer _/_/_/ _/_/_/ ISSO - Software Mastering _/ _/ _/_/_/ _/ _/ _/ i n v e n t [EMAIL PROTECTED] -----Original Message----- From: Ronald J Kimball [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 2:08 PM To: Ian Harisay Cc: [EMAIL PROTECTED] Subject: Re: Transferring from Access to MySQL On Mon, Apr 22, 2002 at 02:55:38PM -0600, Ian Harisay wrote: > The other question is how often will you do this? Is it a one time shot? > If so, do what is easiest to implement. If you know how do it using > inserts, then do it. Don't debate it. The amount of time you might have > saved will be long gone if you don't just act now. Once again, if this > is a one time shot. If it isn't and you really need to speed up a > nightly process.........well, then I'll just shut up. Although this advice is good in general, it may not apply in the specific case of loading large amounts of data into a database. The amount of time you will save depends strongly on the amount of data, the indexes that have to be updated, and the constraints that have to be checked. For example, we recently had to fill a new table in Oracle with approximately 30 million rows, based on data from other tables. I had initially written a very naive PL/SQL approach that selected from existing tables and inserted the new rows one at a time. This ran for a couple days, generated a large amount of redo logs, and still hadn't finished. Then we came up with a much better approach: dump the results of a select to a flat file, and load the file in with SQL*Loader. This approach took a matter of hours, without filling up the disk with redo logs. So, my point is, where you have a really huge data set, optimization even for a single run can be a big win. Ronald
