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:

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:

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:

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:

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

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

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.