Populate table with data stored in separate tables

I have a MySQL database separate_data, which consists of 200 tables with random names (for example first_table, second_table, …) Each of these tables have the same structure and indexes. The total size is about 30GB.

I would like to copy all this data to a new MySQL database called new_database, in the table all_data, keeping the structure (but dropping the auto_increment column), and adding a column to indicate the original table.

So, for example, the situation is:

first_table

+----+------+------+
| id | Col2 | Col3 |
+----+------+------+
|  1 | aaa  | xxx  |
|  2 | aaa  | yyy  |
|  3 | bbb  | zzz  |
+----+------+------+

second_table

+----+------+------+
| id | Col2 | Col3 |
+----+------+------+
|  1 | aaa  | xxx  |
|  2 | ccc  | yyy  |
|  3 | ddd  | zzz  |
+----+------+------+

And the resulting all_data table should be

+------+------+---------------+
| Col2 | Col3 | Original      |
+------+------+---------------+
| aaa  | xxx  | first_table   |
| aaa  | yyy  | first_table   |
| bbb  | zzz  | first_table   |
| aaa  | xxx  | second_table  |
| ccc  | yyy  | second_table  |
| ddd  | zzz  | second_table  |
+------+------+---------------+

The problem is the amount of data. Doing this in PHP is not an option because of that. I’m fine with some manual work (for example creating a data dump for separate_data first and then injecting this data dump into the new database or something like that.

Answer

You seem to want union all:

create table all_data as
select col2, col3, 'first_table' as original from first_table
union all select col2, col3, 'second_table' from second_table

You would typically expand the query with more union all members for other related tables.

Advertisement

Answer