Needed: An Efficient, reliable and smart way to copy data from one DB to another

Ok, I have a prototype running, or I should say crawling, to copy data from
an MS Access database over to an MySQL database. I'd already set up the
tables and indeces, and was using a Java program to select all of the
contents from each table in the Access DB and insert each row from the
resulset into the MySQL. The largest table has about 7700 rows and a couple
dozen columns, It only takes a couple minutes to get the data from the
Access DB, but it seems to take hours to insert it into the MySQL,
undoubtedly because I've so far used the simplest approach: i.e. create a
prepared statement, and iterate through the resultset, populating the
parameters for the prepared statement and then executing it before
proceeding to the next.

I need a way to copy the data over that is much faster. One options I can
see, but which appears to preclude using prepared statements, is to manually
parse the resultset obtained from Access to construct a really long SQL
statement (i.e. with each row consisting of comma separated values, enclosed
in parantheses, and separated from the next row by a comma). There is a
question of how many rows ought to be inserted in one SQL statement. A
second option suggested itself when I noticed that prepared statements have
an addBatch member function. It wasn't clear how to use it, though, since
it doesn't appear to take any parameters, and yet the description says it
adds parameter values to a batch of SQL statements.

I do not know if either of these options I'm considering would improve
things. Are there any other options I ought to consider? I need to get
this done since I will need to deploy my application on a linux box.

Thanks,

Ted

--
R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Solutions
http://www.randddecisionsupportsolutions.com/
Healthy Living Through Informed Decision Making


.

Relevant Pages

  • Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
    ... > data from an MS Access database over to an MySQL database. ... > prepared statement and then executing it before proceeding to the ... > a really long SQL statement (i.e. with each row consisting of comma ...
    (comp.lang.java.databases)
  • Re: Getting a Mysql auto increment value back into my Java client GUI?
    ... Mysql to automate the data management and other tasks for my Dongo ... To insert a country in my mysql country table I use the code below ... preferably with the one sql statement I'm already using to insert. ... The use of a prepared statement is also preferable to hard-coding quotes into an SQL query string as you did in your code snippet. ...
    (comp.lang.java.databases)
  • Re: [PHP] Why do I always come up with the hard stuff?
    ... That did the trick on the connection problem, ... But I want to use a prepared statement for inserting into the database, and my understanding is I can't use prepared statements in the old mysql... ...
    (php.general)
  • RE: Prepare error
    ... On 20-Feb-2006 Adam Boswell wrote: ... supported in the prepared statement protocol yet". ... runs and it sends it to mysql and works, BUT then the process never finishes ...
    (perl.dbi.users)
  • Re: Getting a Mysql auto increment value back into my Java client GUI?
    ... preferably with the one sql statement I'm already using to insert. ... assuming that your table has columns named countryCode and countryName ... The use of a prepared statement is also preferable to hard-coding quotes ... into an SQL query string as you did in your code snippet. ...
    (comp.lang.java.databases)