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


"Ted Byers" <r.ted.byers@xxxxxxxxxx> wrote in message
news:qdCdnWZWv728YR7eRVn-rg@xxxxxxxxxxxxx
> 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

There's an easier way -
create a DSN for your SQL database
Then right-click the table in Access and choose 'Export'
You can export the whole table easily that way.
I used this method to export tables from Access to mySQL, so I'm sure it
will also work to MS-SQL.


.