Combining 2 Databases?

[chaz]

Is it possible to combine 2 DBs quickly in sqlyog? 2 similar databases

1. drop the primary key, which is autoincremental

2. export the data

3. import to new DB which autoicremtns the primary key

4. checking for duplicates and ignore duplicates

eg

2 DBs: DBold and DBnew

ID- Dropped

name

surname (checked for dupes)

and insert if both names are not present, ignore if present

[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