Learn how to copy data from one table into a new table

Learn how to copy data from one table into a new table by using SQL CREATE TABLE and SELECT statement. Copying data from an existing table to a new one is useful in some cases such as backing up data, create a copying of real data for testing.

In order to copy data from one table to a new one you can use the following command:
1 CREATE TABLE new_table
2 SELECT * FROM existing_table

MySQL will first create a new table with name as indicated after CREATE TABLE statement, new_table in this case. Then it will fill the new table with all the data from an existing table (existing_table).

To copy a part of data from an existing table, you can use WHERE clause to filter the selected data base on conditions. The command is as follows:
1 CREATE TABLE new_table
2 SELECT * FROM existing_table WHERE conditions

It is very important to check whether table you want to create is existed or not, you should use IF NOT EXIST after CREATE TABLE statement. The full sql command of copying data from an existing table to a new one will be as follows:
1 CREATE TABLE IF NOT EXISTS new_table
2 SELECT * FROM existing_table
3 WHERE conditions

Here is the example of using copying data command. We have Office data table, now we can copy the table from this table into a new one by using the following command:
1 CREATE TABLE IF NOT EXISTS offices_bk
2 SELECT * FROM offices

If we need only copy all offices in US, so we can use WHERE condition for it as follows:
1 CREATE TABLE IF NOT EXISTS offices_usa
2 SELECT * FROM offices
3 WHERE country = 'USA'