Cloning table in MySQL

In some situations, such as backing up data and duplicating the production data for testing, copying data from an existing table to a new one is highly helpful. With the help of the MySQL copy or clone table feature, we may make an exact replica of an existing table, complete with its structure, indexes, constraints, default values, etc. In a circumstance like backing up data in case of table failure, copying data from an existing database onto a new table is quite helpful.

You may use the CREATE TABLE and SELECT queries to replicate data from one table to another as seen below −

existing_table

CREATE TABLE new_table 
SELECT col, col2, col3 
FROM
    existing_table;

The Construct TABLE command instructs MySQL to first create a new table with the specified name. The SELECT statement's result set determines the new table's structure. The data from the SELECT command is then added to the new table by MySQL.

You may use the WHERE clause in the SELECT statement to replicate a portion of the data from an existing table to the new one as seen below −

CREATE TABLE new_table 
SELECT col1, col2, col3 
FROM
    existing_table
WHERE
    conditions;

The aforementioned sentence merely duplicates the table and its contents, as you can see. It doesn't duplicate any other database objects that are related to the table, such as triggers, primary key constraints, foreign key constraints, and indexes.

Use the following statements to replicate data from one table and all of its dependent objects −

CREATE TABLE IF NOT EXISTS new_table LIKE existing_table;

INSERT new_table
SELECT * FROM existing_table;

Two statements need to be put into action. By replicating the existing table, the first line generates a new table called the new table. Data from the current table is inserted into the new table in the second statement.

new_table

MySQL copy the table to another database

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;

By copying the current table (existing table) from the source database (source dB), the first line generates a new table in the destination database (destination dB).

The second statement transfers data from the source database's existing table to the destination database's new table.

The previously mentioned example is also true for cloning tables to another database.

Conclusion

Whichever method you choose, it's important to ensure that you have appropriate permissions to create and modify tables in your MySQL database. Additionally, be mindful of any data constraints or relationships that may exist between tables, as copying data indiscriminately can lead to data inconsistencies and errors.

Hardik Gupta

Updated on: 06-Apr-2023

85 Views

Kickstart Your Career

Get certified by completing the course

Get Started