Transferring all rows of a MySQL table to another.

Question

I want to bulk insert all rows from one table to another. I am confused on how to use Select with Insert. Is there a way that a new table is automatically created if it does not exist?

Answer 1

trigger and/or select into are recommended here

Answer 2

There are two ways to do this:

One is to INSERT INTO ... SELECT, which will insert the resultset of your query into an existing table with the same data structure as your query

INSERT INTO MyTable_Backup
SELECT * FROM MyTable

The other is to CREATE TABLE ... SELECT ..., which will create a new table based on the data structure of your query an insert the resultset.

CREATE TABLE MyTable_Backup
SELECT * FROM MyTable;

However one thing to note is that this will not match the indexes of the source table. If you need indexes, you need to add them manually.