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:
CREATE
TABLE
new_table
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:
CREATE
TABLE
new_table
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:
CREATE
TABLE
IF
NOT
EXISTS new_table
SELECT
*
FROM
existing_table
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:
CREATE
TABLE
IF
NOT
EXISTS offices_bk
SELECT
*
FROM
offices
If we need only copy all offices in US, so we can use WHERE condition for it as follows:
CREATE
TABLE
IF
NOT
EXISTS offices_usa
SELECT
*
FROM
offices
WHERE
country =
'USA'