How to merge multiple mysql tables in one?

Question

I have a Mysql DB with 121 tables, with different structure, and I need to merge all of this tables in a 1 table.

There are 3 fields that are the same in all tables (email, base_name, location) but all other fields in some tables are the same, in others not.

Is there any wall to merge the tables and conserve all the fields (it doesn't matter if there is null fields)?

Solution 1

I don't know if I understood well, but I guess your looking for joining tables and not merging them (sorry if i missunderstud). I put you an example of a left join, but if you want to keep every record from every table you'll need a full outer join (you'll have to simulate it in mysql cause it's not implemented)

CREATE TABLE table_name AS (

SELECT * 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.email=t2.email AND t1.base_name=t2.base_name AND t1.location=t2.location
  ...
  LEFT JOIN tableN tN ON tN-1.email=tN.email AND tN-1.base_name=tN.base_name AND tN-1.location=tN.location

)

if you want to simulate a full outer join you should union like this:

SELECT * 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.email=t2.email AND t1.base_name=t2.base_name AND t1.location=t2.location
  ...
  LEFT JOIN tableN tN ON tN-1.email=tN.email AND tN-1.base_name=tN.base_name AND tN-1.location=tN.location

UNION

SELECT * 
  FROM tableN tN
  LEFT JOIN tableN-1 tN-1 ON tN.email=tN-1.email AND tN.base_name=tN-1.base_name AND tN-1.location=tN.location
  ...
  LEFT JOIN table1 t1 ON t2.email=t1.email AND t2.base_name=t1.base_name AND t1.location=t2.location

Solution 2

First you will need to manually create the table tha has all of the unique columns in an all of your tables put a primary key on email, base_name, location.

The main problem with this approach is that rows that are matched by the Primary key with identical columns that contain different data will be overwritten by the most recent update.

You can generate an insert statement for each of your tables the following querys will give you the list of columns for your select by comparing table1 to the new table & the columns to update (if not empty).

(change the table number below to generate the data for each of your tables)

SELECT GROUP_CONCAT(NVL(b.COLUMN_NAME,CONCAT('NULL AS ',a.column_name))) as sel_cols
  FROM INFORMATION_SCHEMA.COLUMNS a
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.column_name = b.column_name and b.table_name='table1' and b.table_schema = b.table_schema
  WHERE a.table_name = 'new_table' AND b.table_schema = database()

to get the list of columns to update

SELECT GROUP_CONCAT(CASE WHEN b.column_name IS NOT NULL THEN CONCAT(b.column_name,'=VALUES(',b.column_name,')') ELSE END) as upd_cols
  FROM INFORMATION_SCHEMA.COLUMNS a
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.column_name = b.column_name and b.table_name='table1' and b.table_schema = b.table_schema
  WHERE a.table_name = 'new_table' AND b.table_schema = database()

e.g:

col1, col2, NULL as col3

col1 = VALUES(col1), col2 = VALUES(col2)

now paste the column list & table name into an insert...

INSERT INTO new_table (select col1,col2,NULL as col3 FROM table1)
ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2)

It should be easy to change the sql to generate the exact statements you require, and for all of the tables.

perhaps put a column that will show the fact that an over write has occured and where the original column came from, so you can manually resolve the confict

Solution 3

Of course you can merge tables, it is not even that difficult.
Here's an example for 3 tables, make sure to force all selects in the unions to output the same number of columns:

  SELECT 'table1' as tablename
         , email, basename, location, field1, field2, null, null
  FROM table1
UNION
  SELECT 'table2' as tablename
         , email, basename, location, field1, field2, field3, null
  FROM table2
UNION
  SELECT 'table3' as tablename
         , email, basename, location, field1, null, null, null
  FROM table3
UNION
  ....