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_tableexists. 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. 🙂