How to Copy a Table in MySQL

There are some instances where we may need to copy a specific table from the same or a different database. For example, when performing backups or migrations, we might need to restore a table without restoring the entire database.

This tutorial will go over how to copy a table from a database into another table using MySQL CREATE and SELECT clauses.

Use Cases

The first case where we need to copy a table is copying specific data from an existing table to a new one. For example, in the Sakila sample database, we can copy specific information from the film table to a new table called film_revised.

Consider the query below to perform such an operation:

CREATE DATABASE copy_tb;
use copy_tb;
CREATE table film_revised SELECT title, release_year, length, rating FROM sakila.film;

Once the above query executes, we can view the data stored in the table by using MySQL select statement as shown in the output below:

mysql> SELECT * FROM film_revised LIMIT 10;
+------------------+--------------+--------+--------+
| title | release_year | length | rating |
+------------------+--------------+--------+--------+
| ACADEMY DINOSAUR | 2006 | 86 | PG |
| ACE GOLDFINGER | 2006 | 48 | G |
| ADAPTATION HOLES | 2006 | 50 | NC-17 |
| AFFAIR PREJUDICE | 2006 | 117 | G |
| AFRICAN EGG | 2006 | 130 | G |
| AGENT TRUMAN | 2006 | 169 | PG |
| AIRPLANE SIERRA | 2006 | 62 | PG-13 |
| AIRPORT POLLOCK | 2006 | 54 | R |
| ALABAMA DEVIL | 2006 | 114 | PG-13 |
| ALADDIN CALENDAR | 2006 | 63 | NC-17 |
+------------------+--------------+--------+--------+
10 rows in set (0.00 sec)

As you can see, we can create new tables with selected information from existing tables without altering the data in the original database.

NOTE: Copying a table using the CREATE TABLE and SELECT statements only copies the table and its data. It does not copy objects such as indices, triggers, primary keys constraints, etc., connected to the original table.

Copy Table + Objects

To copy the table + data and all related objects, we use the LIKE statement followed by the INSERT statement as illustrated in the query below:

CREATE TABLE film_copy LIKE sakila.film;

INSERT film_copy SELECT * FROM sakila.film;

The above query will copy everything from the original table into the new one, including indices, primary keys, constraints, and other objects connected to the original table.

NOTE: Be careful when using the copy statements on massive tables as they may use more resources and take a long time to complete.

Copy Tables from Separate Databases

If you need to copy a table from different databases, you can reference the database using the dot (.) notation.

For example, start by creating a new database as:

CREATE DATABASE IF NOT EXISTS multi_db;

Next, use the syntax shown previously to copy a table from the old database to the new one. See the query below:

CREATE TABLE multi_db.new_tb LIKE sakila.film;

INSERT multi_db.new_tb SELECT * FROM sakila.film;

This will copy the table film table from the Sakila database to the new one and display the output as shown below:

Query OK, 1000 rows affected (0.03 sec)

Records: 1000 Duplicates: 0 Warnings: 0

You can use the SELECT statement to verify the data has been copied successfully.

Conclusion

This quick guide has gone over how to use MySQL CREATE TABLE and SELECT statements to copy specific columns from a table to a new table.

We also looked at how to copy all the data, including objects associated with the original table, to a new table.

Finally, we discussed how to copy tables from one database to another.

Thank you for reading.

How to Copy a Table in MySQL

Use Cases

Copy Table + Objects

Copy Tables from Separate Databases

Conclusion

About the author

John Otieno