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.
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.