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. 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 )

Google photo

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