Summary: in this tutorial, you will learn how to copy data from one table into a new one using CREATE TABLE and SELECT statements.
Copying data from an existing table to a new one is very useful in some cases such as backing up data and replicating production data for testing.
In order to copy data from a table to a new table, you can use the following statement:
CREATE TABLE new_table SELECT * FROM existing_table
First, MySQL creates a new table with the name that indicates in the CREATE TABLE statement. Then MySQL selects data from an existing tables and populate it to the new table.
To copy partial data from an existing table to the new one, you use WHERE clause in the SELECT statement as follows:
CREATE TABLE new_table SELECT * FROM existing_table WHERE conditions
It is very important to check whether the table you want to create that already exists before creating it. In order to do so you use IF NOT EXIST in the CREATE TABLE statement. The full command of copying data from an existing table to the new one is as follows:
CREATE TABLE IF NOT EXISTS new_table SELECT * FROM existing_table WHERE conditions
MySQL copy table example
Here is the example of using the copy table command discussed in the above section. We have offices table in the sample database. Now we can copy its data to a new one by using the following statement:
CREATE TABLE IF NOT EXISTS offices_bk SELECT * FROM offices
If you need only copy all offices in the U.S., you can use WHERE clause as follows:
CREATE TABLE IF NOT EXISTS offices_usa SELECT * FROM offices WHERE country = 'USA'