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.

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

1 Answer 1

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

4
  • Great.. Thankyou.. I'll give it a try.. Commented Aug 18, 2013 at 2:38
  • Excellent mate, It works perfectly. Thank you very much for your time. Commented Aug 18, 2013 at 2:48
  • I added a shell script to help generate it, if it will help. Commented Aug 18, 2013 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. Commented Aug 18, 2013 at 3:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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