Copy a table with MySQL

Hello,

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

Advertisements

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:

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