MySQL - Copying a Table

[Geat]

Is there a function to duplicate a table within a database, short of using PHP to get all of the data out of it, recreate the table structure, then insert all of the data back in?

Ideally, it would be like a table rename that doesn't delete the original...

[buntine]

I think you can do it from the command line by simply selecting * records from one table; creating a new table with CREATE TABLE(); and finally, using INSERT INTO to store the new records..

Do you understand what i mean?

Regards,
Andrew Buntine.

[Geat]

Yeah, thanks mate!

I actually found you can do it all in one fell swoop:


CREATE TABLE copyname SELECT * FROM originalname

[buntine]

Nice peice of code, that! I will have to remember it.

Regards.

[Geat]

Yeah, it's pretty mad - I scoured the web and mysql.com for quite a while trying to find it, and people were coming up with all these weird and wonderful solutions, but no-one mentioned that. Finally found it in a professional site on the 8th Google results page or something!