Combining 2 Databases?

[peterlaursen]

I think the basic solution to your problem is to use the SQL syntax “INSERT IGNORE INTO…” or “REPLACE INTO…” instead of just “INSERT INTO”.

If your old/source table is like

CREATE TABLE `oldtest` (

`ID` int(10) unsigned NOT NULL auto_increment,

`n` int(11) default NULL,

`t` varchar(50) default NULL,

PRIMARY KEY (`ID`)

)

then create a new/target table like (define a PK on ALL columns of the table)

CREATE TABLE `test` (

`n` int(11) NOT NULL,

`t` varchar(50) NOT NULL,

PRIMARY KEY (`n`,`t`)

)

Now you need to read values from source (`oldtest`) and for every row in source execute

INSERT IGNORE INTO newtest (n,t)

   values (n_value_for_the_source_row,t_value_for_the_source_row);

(or REPLACE INTO…)

INSERT IGNORE INTO will skip dublicate rows in target, REPLACE INTO will overwrite, but the result will be the same: only 1 row with the same data!

Now you can ALTER TABLE, drop the 'intermediate PK', create a new ID column and define it as the PK.

However I cannot figure any way to do this in 'pure' SQL.  I think you have two options:

1) using an external script/application reading the source on a per row base and INSERT IGNORE/REPLACE INTO the target.

2) use a Stored Procedure (with a cursor that 'runs through' the source row-by-row and does the same)

[peterlaursen]

BTW: SQLyog (ENTERPRISE/ENTERPRISE TRIAL) Migration Tool can do that easy for you!

With my example as in the previous post do this:

1) Create the target table in advance in another database than source

2) Create a DNS with teh MyODBC driver 3.51 pointing to the datbases of the source

3) Migrate from Source to Target with the Migration Tool:

— in the 'map' dialogue uncheck the current PK column

— use 'advanced' setting like attached screenshot (the Migration Tool will REPLACE INTO)

4) ALTER TABLE target: dorp the PK on all columns, create the new ID-column and define it as a PK!

[peterlaursen]

You inspired me to write a FAQ:

http://www.webyog.com/faq/17_151_en.html

[Author]

Posts