Skip to content
Advertisement

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

second_table

And the resulting all_data table should be

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.

Advertisement

Answer

You seem to want union all:

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement