Copying data from MS Access to MySQL 5.015

OK, I wrote a little program in java to retrieve the data from the MS Access
database and insert it into the MySQL database. Retrieving even the largest
table, about 7700 records, from the Acceess database takes only a few
minutes, but inserting the data into the corresponding table in MySQL takes
hours. I don't understand why, but I suspect that it may be related to the
fact I get the data from Access as a single block of data (one SELECT
statement), and I insert the records one at a time into MySQL.

For each table, I created a prepared statement, using java. I then iterate
through the recordset obtained from the Access database, populate the
parameters for the statement and execute it.

I guess this is as much a question about JDBC in Java as it is a question
about MySQL. However, if there is an easy way to transfer the data from
Access to MySQL that uses another language (e.g. Perl or PHP) I can use it
as I have a variety of programming tools (not .net or VB though).

I took the obvious, brute force approach (and it works after a fashion - it
is just painfully slow), but I hope there is a better, smarter way.

Does anyone know a better, faster and more reliable way to transfer table
data from one DB to another? Is there any hope that I missed something
useful in JDBC? Would transforming my prepared statements into stored
procedures make a difference?

I should add that though the source databsase is an MS Access database, I do
not have Access installed. Instead I added it as an ODBC database for the
system and then use the JDBC-ODBC bridge driver to access it from within a
Java program.

I'd appreciate any suggestions you may have.

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

>OK, I wrote a little program in java to retrieve the data from the MS Access
>database and insert it into the MySQL database. Retrieving even the largest
>table, about 7700 records, from the Acceess database takes only a few
>minutes, but inserting the data into the corresponding table in MySQL takes
>hours. I don't understand why, but I suspect that it may be related to the
>fact I get the data from Access as a single block of data (one SELECT
>statement), and I insert the records one at a time into MySQL.

MySQL allows you to insert multiple records at a time with one INSERT.

INSERT INTO addrbook values (name, addr, phone) values
('George W. Bush', '1400 Pennsylvania Avenue', '555-1212'),
('Al Gore', 'The Internet', '1-900-666-6666'),
('Osama Bin Laden', '1400 Pennsylvania Avenue', '555-1212');

I'm not sure how long you can make a query like this, but I suspect
it's good for a thousand records of the size shown above in one
query. For additional examples, see the output of 'mysqldump'.

Gordon L. Burditt

Hi Gordon,

Thanks

"Gordon Burditt" <gordonb.4p6qa@burditt.org> wrote in message
news:11o797reo4ce70b@corp.supernews.com...
> >OK, I wrote a little program in java to retrieve the data from the MS
> >Access
>>database and insert it into the MySQL database. Retrieving even the
>>largest
>>table, about 7700 records, from the Acceess database takes only a few
>>minutes, but inserting the data into the corresponding table in MySQL
>>takes
>>hours. I don't understand why, but I suspect that it may be related to
>>the
>>fact I get the data from Access as a single block of data (one SELECT
>>statement), and I insert the records one at a time into MySQL.
>
> MySQL allows you to insert multiple records at a time with one INSERT.
>
> INSERT INTO addrbook values (name, addr, phone) values
> ('George W. Bush', '1400 Pennsylvania Avenue', '555-1212'),
> ('Al Gore', 'The Internet', '1-900-666-6666'),
> ('Osama Bin Laden', '1400 Pennsylvania Avenue', '555-1212');
>
> I'm not sure how long you can make a query like this, but I suspect
> it's good for a thousand records of the size shown above in one
> query. For additional examples, see the output of 'mysqldump'.
>
That was on my list of things to try.

There is another option that just made it to the top of my list.
Apparently, once I have created a DNS for the Access database, I can export
tables from it. I can try the export now, since I already have the DNS, but
I am not sure how to import the resulting file into MySQL.

Thanks again,

Ted

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

There is a much easier way. I visited the MySQL site today and found they
have a new migration tool. I used it to transfer the data in a matter of
minutes. Now I'd like to see the source code for that tool so I can see how
they did it so quckly.

Cheers,

Ted

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