Copy a table with MySQL


If you want to copy all content from one table to another table with the same structure and data, it’s easy using MySQL syntax.

mysql> CREATE TABLE new_destination_table SELECT * FROM source_table;

It will create a new table with the same content of the source table.

See you!

EDIT: As Arjen Lentz said in his blog, this is not the best way to copy a table in Mysql. So, ignore this post if you don’t need to copy just the simple structure without indexes :)


6 thoughts on “Copy a table with MySQL

  1. Not exactly. If you do it that way, you’ll lose indexes and some other table attributes. To make an exact copy you need two steps

    mysql> CREATE TABLE new_destination_table LIKE source_table;
    mysql> INSERT INTO new_destination_table SELECT * FROM source_table;

  2. Yes Jorge, you’re right, I had already planned to update the post (following Arjen lesson :) but you came before. Thanks anyway for the clarification.

  3. Create table like does not make an exact copy, constrainst of the original table are not copied.

  4. Hi,

    you may use the query select * into A from B

    where A – new table name, B- old table name

    the table is automatically created and contents are copied.. it works fine with SQL 2005

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s