How to copy a table in MySQL database

Many developers have tried to copy a table, they usually end up with an error message telling them that the normal “SELECT INTO” sql code that is useful in creating a backup of a table, has an error in line 1.

Here is how to do this and achieve the same result.

  • You have to create a table with the same fields that you are interested in backing up or copying. Then you run this code in your database

    INSERT INTO bkup_table SELECT fields1, ... FROM main_table [WHERE ...]

    Before running this code, ensure the table bkup_table exists. Also the number of columns to insert data into must match the number of columns selected for export.

  • The second option is the most widely adopted as this creates the backup table on the fly.

    CREATE TABLE bkup_table SELECT * FROM main_table [WHERE ...]

    The table copied would not contain index definitions and other meta-table information.

  • Another way to copy data from a table in another database to another table in another database:

    INSERT into db1.table (field1*) select field1 from db2.table

    * field list in the first part if the query (INSERT …) has to match the fields list in the second part of the query (SELECT …)

    Hope someone finds this very useful. 🙂