Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
show 2 more comments

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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