Copying a table in the same DB ?

[Stinger51]

I can't find in the MySQL manual how to do this. I want to duplicate an existing table that I have, as it will have the same structure as the existing one, only contain a different set of data. I figured it would be easier to copy it, and then just delete all the records in the new table, rather than build it from scratch.

Anybody know how to make a copy of a table in the same DB? And is it possible to just copy the structure and not the data? Export won't work here, as I'm not going between databases.

[DoD]

PHPMyAdmin i think can do this

Make structual backup, alter sql file with new table name, and run the sql file on it.

[[deleted]]

" I figured it would be easier to copy it, and then just delete all the records in the new table, rather than build it from scratch."

How do you figure? Creating a new table takes one query, copying and then deleting takes at least two queries. 🙂

Anyway, to copy a table you can literally select it into a new table and mysql will copy the table definition too.

http://www.mysql.com/doc/en/CREATE_TABLE.html

about 3/4 down the page there is this:

If you specify a SELECT after the CREATE statement, MySQL will create new fields for all elements in the SELECT.

Read that part, don't start experimenting right now, read it first!