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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: