MySQL Copy Tables

Summary: in this tutorial, you will learn how to copy tables within the same database or from one database to another using CREATE TABLE ... SELECT statement.

Copying tables within the same database

Copying data from an existing table to a new one is very useful in some cases such as backing up data and replicating the production data for testing.

To copy data from a table to a new one, you use CREATE TABLE... SELECT statement as follows:

CREATE TABLE new_table 
SELECT column1, column2, ...
FROM existing_table;Code language: SQL (Structured Query Language) (sql)

In this syntax;

First, specify the name of the new table in the CREATE TABLE clause.
Second, construct a query that retrieves data from an existing table.

This statement creates a new table new_table, and copy data from the existing_table to the new one based on the result set of the SELECT clause.

To copy partial data from an existing table to the new one, you use the WHERE clause in the SELECT statement as follows:

CREATE TABLE new_table 
SELECT column1, column2, ...
FROM existing_table
WHERE condition;Code language: SQL (Structured Query Language) (sql)

It is very important to check whether the table you want to create already exists before creating it.

To do so, you can use IF NOT EXIST option in the CREATE TABLE statement.

The complete statement of copying data from an existing table to a new one is as follows:

CREATE TABLE IF NOT EXISTS new_table 
SELECT column1, column2, ...
FROM existing_table
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Note that this statement copies the table and its data. It does not copy other database objects associated with the existing table such as indexes, primary key constraints, foreign key constraints, triggers, and so on.

To copy data from one table and also all the dependent objects of the table, you use the following statements:

First, create a new table whose structure is the same as the existing one:

CREATE TABLE IF NOT EXISTS new_table 
LIKE existing_table;

Second, insert data from the existing table into the new table:

INSERT new_table
SELECT * FROM existing_table;

Examples of copying a table within the same database

We’ll use the sample database for the demonstration purposes.

First, create a new table called offices_bk and copy all data from the offices table to the new table:

CREATE TABLE IF NOT EXISTS offices_bk 
SELECT * FROM offices;Code language: SQL (Structured Query Language) (sql)

Second, verify the copy by retrieving data from the office_bk table as follows:

SELECT * FROM offices_bk;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
7 rows in set (0.00 sec)

If you want to copy the offices in the USA only, you can use a WHERE clause in the statement:

CREATE TABLE IF NOT EXISTS offices_usa 
SELECT * 
FROM
    offices
WHERE
    country = 'USA'Code language: SQL (Structured Query Language) (sql)

The following statement gets all the data from the offices_usa table.

SELECT * FROM offices_usa;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
| officeCode | city          | phone           | addressLine1         | addressLine2 | state | country | postalCode | territory |
+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street | apt. 5A      | NY    | USA     | 10022      | NA        |
+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

Suppose, you want to copy not only the data but also all database objects associated with the offices table, we use the following statements:

CREATE TABLE offices2 LIKE offices;

INSERT offices2
SELECT * FROM offices;Code language: SQL (Structured Query Language) (sql)

Copying tables across databases

Sometimes, you want to copy a table to a different database. In such cases, you use the following statements:

CREATE TABLE destination_db.new_table 
LIKE source_db.existing_table;

INSERT destination_db.new_table 
SELECT * FROM source_db.existing_table;Code language: SQL (Structured Query Language) (sql)

The first statement creates a new table new_table in the destination database (destination_db) by duplicating the existing table (existing_table) from the source database (source_db).

The second statement copies data from the existing table in the source database to the new table in the destination database.

For example:

First, create a database named testdb using the following statement:

CREATE DATABASE IF NOT EXISTS testdb;Code language: SQL (Structured Query Language) (sql)

Second, create the offices table in the testdb by copying its structure from the offices table in the classicmodels database.

CREATE TABLE testdb.offices LIKE classicmodels.offices;Code language: SQL (Structured Query Language) (sql)

Third, copy data from the classimodels.offices table to testdb.offices table.

INSERT testdb.offices
SELECT *
FROM classicmodels.offices;Code language: SQL (Structured Query Language) (sql)

Finally, verify the data from the testdb.offices table.

SELECT * FROM testdb.offices;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned various techniques to copy tables within a database and from one database to another.

Was this tutorial helpful?