Professional Web Applications Themes

Copying data from MS Access to MySQL 5.015 - MySQL

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 ...

  1. #1

    Default 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
    [url]http://www.randddecisionsupportsolutions.com/[/url]
    Healthy Living Through Informed Decision Making


    Ted Byers Guest

  2. #2

    Default Re: 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.
    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
    Gordon Burditt Guest

  3. #3

    Default Re: Copying data from MS Access to MySQL 5.015

    Hi Gordon,

    Thanks

    "Gordon Burditt" <gordonb.4p6qaburditt.org> wrote in message
    news:11o797reo4ce70bcorp.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
    [url]http://www.randddecisionsupportsolutions.com/[/url]
    Healthy Living Through Informed Decision Making


    Ted Byers Guest

  4. #4

    Default Re: Copying data from MS Access to MySQL 5.015

    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
    [url]http://www.randddecisionsupportsolutions.com/[/url]
    Healthy Living Through Informed Decision Making


    Ted Byers Guest

Similar Threads

  1. Can CF MX 6.1 access MySQL database as a Data Source?
    By EQPro in forum Coldfusion Server Administration
    Replies: 6
    Last Post: November 28th, 04:28 PM
  2. Copying data from one Access database to another
    By Dave Henson in forum Coldfusion Database Access
    Replies: 1
    Last Post: August 10th, 03:11 PM
  3. Forms...Copying data from one table to another
    By Paul G. in forum Microsoft Access
    Replies: 2
    Last Post: July 16th, 05:21 PM
  4. copying image data
    By RP in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 06:09 PM
  5. Beginner - copying data from one table to another
    By Mark Muller in forum Oracle Server
    Replies: 2
    Last Post: December 6th, 09:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139