Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- From: Mark Matthews <mark@xxxxxxxxx>
- Date: Tue, 29 Nov 2005 12:26:52 -0600
Ted Byers wrote:
> "Hal Rosser" <hmrosser@xxxxxxxxxxxxx> wrote in message
> news:plbhf.9624$KP1.1427@xxxxxxxxxxxxxxxxxxxxxxxxx
>> [snip]
>> 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.
>>
>>
> 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
>
Ted,
They did it using JDBC, but the migration toolkit uses "bulk" inserts of
the form:
INSERT INTO [table] VALUES (...),(...),(...)
This form of insert statement is optimized inside the MySQL server in
that it does the index update(s) all in one go for all elements of the
insert, rather than row-by-row as a simple single-row insert would do.
The migration toolkit will build a query of this size up to 5MB or so,
or the maximum sized query that your MySQL server is confiugred to accept.
My guess is that your entire table (or at least most of it) was sent as
a single statement to MySQL, which is why this method is much quicker
than the other alternatives you tried.
-Mark
.
- References:
- Prev by Date: Signature Capture Applet - Version 2
- Next by Date: Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- Previous by thread: Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- Next by thread: Re: Needed: An Efficient, reliable and smart way to copy data from one DB to another
- Index(es):
Relevant Pages
|
