How To Copy Table in MySQL?

How To Copy Table in MySQL?

MySQL is a relational database management system that uses SQL language to perform CRUD operations on the data stored in tables inside a database. SQL stands for Structured Query Language, a querying language to fetch results from the database.

Copying table in MySQL is an operation that can be useful in creating a backup of an existing table. It can be done by copying the entire table structure along with its data into another table which eventually creates a backup of an existing table. There also might be some instances where we want to copy a table from the same or different databases.

For example, suppose we have a product table that contains details about products like prices, id, etc. Let's say we have to create the same product table for the international market but without changing anything in the original table therefore, we will create a new product table by copying the original table.

There are no direct approaches or clauses which can be used to copy a table instead we have to use alternatives to perform the copy table MySQL operation. Copy table MySQL can be achieved using several ways like using SELECT, LIKE clauses with the CREATE TABLE statement. Let's discuss them in detail along with their syntaxes.

CREATE TABLE ... AS SELECT Statement

When we use the CREATE TABLE statement along with the SELECT clause then firstly, it creates a new table and secondly, the SELECT clause will define the structure and data of the newly created table that needs to be copied from the original table to the newly created copy table MySQL.

Syntax

The above syntax shows the creation of a new table which is a copy of an existing table with defined fields, and columns.

MуSQL Copy the Table Structure Only

There are different ways in which we can copy an existing table. Here, we will copy the structure of the existing or the structure of the existing table will be used as the base to create a new table. The existing table's structure will be cloned and it will be used in forming a new table. As discussed earlier, if we only want to copy the structure without including the data of the existing table then we have to use the LIKE clause with the CREATE TABLE.

Let's understand how to copy the table structure only with an example.

Example

In this example, we will consider a Product table which will contain fields like product_id, product_type, etc and then we will create a copy table of the original table which will only contain the structure of the original table, not the data.

Let's consider the following table Product:

Code

Output(Product_1 table)

Explanation
In the above example, we have used the LIKE clause to form a new table Products_1 based on the structure of the Products table means it will contain only the structure of the Products table. This is how copy of the Products table is formed only with table structure.

Copy Table Data to Another Table

After copying the structure of the existing table in the previous section, now we will copy the data of the existing table to another table. The data will be copied or inserted into the new table by using the INSERT clause.

Let's understand the concept with an implemented example.

Example

In this example, we will consider the same table Products and we have also created a copy of the existing table which is Products_1 in the previous example. Now, here we will take one step further and copy the data of table Products to the newly created table Proucts_1.

Let's consider the following table Product:

Code

Output(Product_1 table)

Explanation
Here, we have inserted the data or copied the data of the existing table Products to the newly created copy table Products_1. For this, we have used the INSERT clause in the query followed by the name of the newly created table Products_1. After that, we selected complete data from the Products table using the SELECT clause.

Copy a Table from one Database to Another

Previously, we created a new table and copied the existing table's data into it within the same database. But here, we will copy a table from a different database using the clauses we have used in earlier examples.

Let's understand this concept with an implemented example.

Example

In this example, we will copy a table Products from one database to another database.

Let's consider the following table Products from database info_1:

Now, let's structure a query to copy the above-given table into a different database in a different table.

Code

Output(Product_2 table)

Explanation
In the above example, we have created a new table Products_2 in the info_2 database by copying the original table Product from the info_1 database. We inserted the data of the Product table from the info_1 database in the Product_2 table from the info_2 database.

Copy a Table to a New Table

As we have discussed earlier, several methods and ways are used to create a copy of a table. But there may be scenarios where we want to filter out some of the fields, columns, etc, which we want in the existing table's copy. Therefore, we select some specific fields using the SELECT clause along with the CREATE TABLE which is used to create a new table.

Let's understand this concept with an implemented example.

Example

Let's consider the table Product used in previous examples. Here, we will copy a table Product already exists in the database and create a new table with some specific columns of the Product table.

Let's consider the following table Products:

Code

Output(Product_info table)

Explanation
In the above example, we have created the copy of table Products which is Products_info using the CREATE TABLE clause. After which we have selected some specific columns using the SELECT clause which has to be copied to the new table Products_info from the existing table Products. Therefore, the new table Products_info will only contain selected columns that can be seen in the output.