MySQL – Copy Table
The MySQL copy or clone table feature allows us to duplicate an existing table, including its structure, indexes, constraints, default values, etc. The ability to copy data from an existing table into a new table is very useful when backing up data in the event of a table failure. Also, it is useful when we need to test or perform something without affecting the original table, such as replicating production data for testing purposes.
We It is possible to copy an existing table into a new table by using the CREATE TABLE and SELECT statements, as shown below:
CREATE TABLE new_table_name SELECT column1, column2, column3 FROM existing_table_name;
First, it creates a new table according to the CREATE TABLE statement. In addition, the result set of a SELECT statement determines the structure of a new table. Lastly, MySQL fills the newly created table with data obtained from the SELECT statement.
If In order to copy only partial data from an existing table to a new table, use the WHERE clause with the SELECT statement as follows:
CREATE TABLE new_table_name SELECT column1, column2, column3 FROM existing_table_name WHERE condition;
Ensure that the table we are going to create does not already exist in our database. MySQL’s IF NOT EXISTS clause allows us to check whether a table already exists before creating a new one. The following statement explains it better:
CREATE TABLE IF NOT EXISTS new_table_name SELECT column1, column2, column3 FROM existing_table_name WHERE condition;
It should be noted that this statement only copies the table and its data. In addition to indexes, triggers, primary key constraints, foreign key constraints, etc., it does not copy all dependent objects of the table. Therefore, the command to copy data along with its dependent objects from an existing table to a new table can be expressed as follows:
CREATE TABLE IF NOT EXISTS new_table_name LIKE existing_table_name; INSERT new_table_name SELECT * FROM existing_table_name;
Based on the above example, we can see that we need to execute two statements in order to copy data along with its structure and constraints. The first command creates a new table named new_table_name that duplicates the existing table, and the second command adds data from the existing table to the new table.
With the help of an example, let us demonstrate how we can create a duplicate table. The first step is to create a table named “original” using the following statement:
CREATE TABLE original ( Id int PRIMARY KEY NOT NULL, Name varchar(45) NOT NULL, Email varchar(45) DEFAULT NULL, Country varchar(25) DEFAULT NULL, Year int NOT NULL );
The next step is to add values to this table. The following statement should be executed:
INSERT INTO original( Id, Name, Email, Country, Year) VALUES (1, 'venu', 'email@example.com', 'USA', 2015), (2, 'Madhu', 'firstname.lastname@example.org', 'India', 2016), (3, 'lakshmi', 'email@example.com', 'USA', 2016), (4, 'Ragu', 'firstname.lastname@example.org', 'England', 2015);
To display the records, execute the following SELECT statement:
SELECT * FROM original;
The following statement copies data from the existing table “original_table” to a new table named “duplicate” in the selected database.
CREATE TABLE IF NOT EXISTS duplicate SELECT * FROM original;
Using the SELECT statement, we can verify the table data after the execution has been successful. The following output can be seen:
SELECT * FROM duplicate;