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'

Related Tutorials

• Creating Tables Using MySQL CREATE TABLE Statement

• Using MySQL SELECT Statement to Query Data

原文链接：http://outofmemory.cn/mysql/tips/mysql-copy-table-data