3

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.

  • Are all the tables MyISAM ??? – RolandoMySQLDBA Aug 18 '13 at 2:26
  • @RolandoMySQLDBA Yes – Giri Aug 18 '13 at 2:26
  • Is merged_table to be MyISAM also ??? – RolandoMySQLDBA Aug 18 '13 at 2:29
  • @RolandoMySQLDBA Yes – Giri Aug 18 '13 at 2:29
  • @RolandoMySQLDBA If innodb is best, then let's make it as innodb – Giri Aug 18 '13 at 2:30
3

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

  • Great.. Thankyou.. I'll give it a try.. – Giri Aug 18 '13 at 2:38
  • Excellent mate, It works perfectly. Thank you very much for your time. – Giri Aug 18 '13 at 2:48
  • I added a shell script to help generate it, if it will help. – RolandoMySQLDBA Aug 18 '13 at 2:53
  • Thanks for your shell script. I hope it will be useful for others. I've already used a php for loop to generate that table code you given. – Giri Aug 18 '13 at 3:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.