How can I merge two MySQL tables

Question

How can I merge two MySQL tables that have the same structure   The primary keys of the two tables will clash  so I have take that into account

User · Answer

You could write a script to update the FK's for you.. check out this blog: http://multunus.com/2011/03/how-to-easily-merge-two-identical-mysql-databases/

They have a clever script to use the information_schema tables to get the "id" columns:

SET @db:='id_new'; 

select @max_id:=max(AUTO_INCREMENT) from information_schema.tables;

select concat('update ',table_name,' set ', column_name,' = ',column_name,'+',@max_id,' ; ') from information_schema.columns where table_schema=@db and column_name like '%id' into outfile 'update_ids.sql';

use id_new
source update_ids.sql;

User · Answer

INSERT INTO    first table f SELECT    FROM    second table s ON DUPLICATE KEY UPDATE         s column1   DO WHAT EVER MUST BE DONE ON KEY CLASH f column1

User · Answer

If you need to do it manually  one time   First  merge in a temporary table  with something like   create table MERGED as select   from table 1 UNION select   from table 2   Then  identify the primary key constraints with something like  SELECT COUNT     PK from MERGED GROUP BY PK HAVING COUNT     gt  1   Where PK is the primary key field     Solve the duplicates   Rename the table    edited - removed brackets in the UNION query  which was causing the error in the comment below

User · Answer

It depends on the semantic of the primary key  If it s just autoincrement  then use something like   insert into table1  all columns except pk  select all columns except pk  from table2    If PK means something  you need to find a way to determine which record should have priority  You could create a select query to find duplicates first  see answer by cpitis   Then eliminate the ones you don t want to keep and use the above insert to add records that remain

User · Answer

You can also try   INSERT IGNORE   INTO table 1  SELECT     FROM table 2          which allows those rows in table 1 to supersede those in table 2 that have a matching primary key  while still inserting rows with new primary keys   Alternatively    REPLACE    INTO table 1  SELECT      FROM table 2           will update those rows already in table 1 with the corresponding row from table 2  while inserting rows with new primary keys

User · Answer

Not as complicated as it sounds     Just leave the duplicate primary key out of your query     this works for me    INSERT INTO   Content       status       content category      content type      content id      user id      title      description      content file      content url      tags      create date      edit date      runs     SELECT  status     content category    content type    content id    user id    title    description    content file    content url    tags    create date    edit date    runs FROM   Content Images

[sql] How can I merge two MySQL tables?

Examples related to sql

Examples related to mysql

Examples related to merge