Mysql: Create new table exactly like another one

Lets say you have a table at your mysql. Now you want to create a new table which will have exactly the same table structure of old one. Using the LIKE keyword of mysql you can easily create you desired table.

mysql> CREATE TABLE new_table LIKE old_table;

This will create a table "new_table" which will have exactly the same structure of "old_table". Remember, the data will not copy to the "new_table". You have to write other command to do that. The command for creating the table with data will be,

mysql> CREATE TABLE new_table SELECT * FROM old_table;

This will create the "new_table" which will have the same structure "old_table", and will have all the data into it. You can filter the data with WHERE condition at SELECT query. for example,

mysql> CREATE TABLE new_table SELECT * FROM old_table WHERE id > 10;

You can copy the data using INSERT command after creating a table using LIKE keyword. The mysql query will be,

mysql> INSERT INTO new_table SELECT * FROM old_table;

Comments

Thank you so much for this very straightforward description of the three commands. It proved very helpful, and I am in your debt.

Brian in NY
Anonymous said…
very helpful..thank you very much..
Anonymous said…
AS simple as that. Thanks mate :)
cheesy_chocaholic said…
Very useful! Thanks! I had seen a format using 'AS' before but have not found it since so was struggling to get what I needed. This helped clear things up :)
Andy said…

mysql> CREATE TABLE new_table SELECT * FROM old_table;

This will not always create a new table with the same structure as the old table. It will create a table which fits the data exactly.

For example - you have a field which is a VARCHAR(30) but the longest string in that field is only 20 chars long - the new table will be initialised as a VARCHAR(20).

The correct procedure would be to create a new table in the likeness of the old:

mysql> CREATE TABLE new_table LIKE old_table;

and then copying the data in:

mysql> INSERT INTO new_table SELECT * FROM old_table;