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_tableMySQL 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_tableSELECT * FROM existing_table WHERE conditionsIt 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_tableSELECT * FROM existing_tableWHERE conditionsHere 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_bkSELECT * FROM officesIf we need only copy all offices in US, so we can use WHERE condition for it as follows:
CREATE TABLE IF NOT EXISTS offices_usaSELECT * FROM officesWHERE country = 'USA'
