inserting data from one table to another table in same database

[rk68]

I have got about 80 tables (same structure/fields) from my client. I want data of all 80 tables in one single table. There are around 90 lakhs records in 80 tables. How do I get to do this ?

TIA
Raj

 

[TonyGroves]

You could run one big query for all the tables:
[tt]
CREATE bigtable
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...
UNION ALL
SELECT * FROM table80
[/tt]

 

[TonyGroves]

Sorry, that should have been:
[tt]
CREATE TABLE bigtable
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...
UNION ALL
SELECT * FROM table80
[/tt]

 

[rk68]

Thanks Tony. But I hope this won't work as all the 80 tables has a primary Key & this may give problem while using the above SQL. I have already created a table & changed the primary key to avoid this error & also inserted records from few tables out of the 80 which had few records. I am using EMS MySQL Manager which has an option to insert data using 'Export Data using Insert'. This creates automatically an insert SQL & insert data into the said table. It is fine for 30000-40000 records. When the no. of records are more than 50000, it doesn't work, maybe memory problem.
Is there any other options ? Some tables have records around 25 lacs.
Any other method available ?

TIA
Raj

 

[TonyGroves]

Using the code I gave, no indexes will be created for the new table, so there is no chance of duplicate primary keys being rejected. (You will of course want to deal with that issue later).

Maybe EMS MySQL Manager imposes a limit on the sizes of datasets it can handle. Other MyQSL clients, like the "mysql" command-line program, have no such limitation. Alternatively, you could try adding "LIMIT 9999999" to the end of each SELECT statement.

25 lakhs (25,00,000 is it?) records in a table should be no problem; it will just take a bit longer.