CODEWITHSUNDEEP
mysql
ilhan
ilhan

Reputation: 8954

How do I merge 5 identical MySQL tables?

I want to merge 5 identical-schema (okay, they are not exactly identical but I can edit the field names to make them identical) MySQL databases into one database. Is there any easy way?

CREATE TABLE `users` (
    `name` VARCHAR (50) COLLATE utf8_turkish_ci DEFAULT NULL,
    `surname` VARCHAR (50) COLLATE utf8_turkish_ci DEFAULT NULL,
    `telephone` VARCHAR (50) COLLATE utf8_turkish_ci DEFAULT NULL,
    PRIMARY KEY (`id`),

) ENGINE = MyISAM AUTO_INCREMENT = 1000 DEFAULT CHARSET = utf8 COLLATE = utf8_turkish_ci PACK_KEYS = 0 ROW_FORMAT = DYNAMIC

Upvotes: 1

Views: 129

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

If the tables are exactly the same with column names/types and are named

  • user1
  • user2
  • user3
  • user4
  • user5

there are two approaches to handle this:

APPROACH #1 : Load the data into one table

CREATE TABLE user LIKE user1;
INSERT INTO user (name,surname,telephone,...)
SELECT name,surname,telephone,... FROM user1;
INSERT INTO user (name,surname,telephone,...)
SELECT name,surname,telephone,... FROM user2;
INSERT INTO user (name,surname,telephone,...)
SELECT name,surname,telephone,... FROM user3;
INSERT INTO user (name,surname,telephone,...)
SELECT name,surname,telephone,... FROM user4;
INSERT INTO user (name,surname,telephone,...)
SELECT name,surname,telephone,... FROM user5;

If the id is auto_increment all rows get new ids.

APPROACH #2 : Use the MERGE Storage Engine

CREATE TABLE user LIKE user1;
ALTER TABLE user
    ENGINE=Mrg_MyISAM
    UNION=(user1,user2,user3,user4,user5)
;

Give it a Try !!!

Upvotes: 1

hd1
hd1

Reputation: 34657

Any ETL tool, like Clover, would be well suited for your purpose. Just define your column mappings and you should be good to go. Leave a comment if you need further help.

Upvotes: 1

Menelaos
Menelaos

Reputation: 25725

There's no easy way to do this.

You could attempt to alter your tables within the different databases to bring them to be in the most similar format.

Additionally, you could use statements such as 

Create table as select

in order to further format the data.

Than you would have to do a MYSQL DUMP of all your databases. Select only the create statements from the database schema you are interested in following, and add your insert statements (for the data) from all the different databases.

You may also have to perform text manipulation in Excel, or from with mysql in order to get the data in such a format that it is compatible and can be inserted in your final schema.

Upvotes: 1

Related Questions