Mysql – Merge multiple tables in a database

myisamMySQLUbuntu

I have a MySQL database with table names like 1,2,3... upto 1000

Each tables have 2000 rows and has the same structure.

This is how all my 2000 table's structure look like

id,url,title,content

id has type int, primary key,auto increment.

Now I would like to create a new database with name merged. Also a table with name merged_table.

Can someone tell me how to import all my tables in merged_table?

My problem here is all my tables has id from 1 to 2000. So I have no idea how to import them.

I would like to have unique ids from 1 to 2000000 in my new merged_table

I'm using ubuntu. So if someone suggest me a terminal command, that would be awesome.

Best Answer

Create the first table, then load each table into the merged_table:

CREATE TABLE merged_table LIKE `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `2`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `3`;
...
...
;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `999`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1000`;

Here is a way to script it (if the database is mydb)

DB="mydb"
MYSQL_CONN="-uroot -ppassword"
echo "CREATE TABLE merged_table LIKE \`1\`;" > MergeData.sql
SQL="SELECT CONCAT('INSERT INTO merged_table (url,title,content)"
SQL="${SQL} SELECT url,title,content FROM \`998\`')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_name REGEXP "$[0-9]"
mysql ${MYSQL_CONN} -ANe"${SQL}" >> MergeData.sql

Look at the output of the file

less MergeData.sql

If it looks like the output I gave at the top of my answer, then run

mysql ${MYSQL_CONN} -D${DB} < MergeData.sql

Give it a Try !!!

Related Question