Synchronizing databases

6 Oct 2006 2:23 PM

chad

Hi Everyone,

I'm experimenting with a vb.net 2.0 win application that is connected
to a
large database (mysql, +-45000 rows). The app has to import/synchronize
changed rows from a 2nd smaller database which has the same table
structure
(but ms access).

I have been playing with the datatable/set merge functionality, but
that is
not giving me the correct results, it always seems to append the rows
as
new. I've tried various combinations of code samples.

I've been thinking of changing the strategy to looping through the
smaller
import database and sending the appropriate UPDATE statements to the
large
database, but initial tests shows it to be quite slow/inefficient.

Can anybody offer advice? Point me in the right direction?

Thanks in advance
Chad

6 Oct 2006 2:40 PM

Cowboy (Gregory A. Beamer)

Import from the smaller database into "temporary" tables. They can be
permanent tables that look like the real tables or created on the fly
(permanent is easier). Follow these steps:

1. Migrate data into temp table
2. Delete identical records from temp table (all fields same)
3. For records that will update main table, run the update and delete the
records
4. Insert the remaining records into the table(s)
5. Make sure all temp tables are cleared out

This is a reuseable architecture that you can do over and over again once
built.

BTW, 45000 rows is not a large database. :-)

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

Show quote

"chad" <c***@zclix.com> wrote in message
news:1160144609.995100.139910@m7g2000cwm.googlegroups.com...
> Hi Everyone,
>
> I'm experimenting with a vb.net 2.0 win application that is connected
> to a
> large database (mysql, +-45000 rows). The app has to import/synchronize
> changed rows from a 2nd smaller database which has the same table
> structure
> (but ms access).
>
> I have been playing with the datatable/set merge functionality, but
> that is
> not giving me the correct results, it always seems to append the rows
> as
> new. I've tried various combinations of code samples.
>
> I've been thinking of changing the strategy to looping through the
> smaller
> import database and sending the appropriate UPDATE statements to the
> large
> database, but initial tests shows it to be quite slow/inefficient.
>
> Can anybody offer advice? Point me in the right direction?
>
> Thanks in advance
> Chad
>