EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Clone Table
 

MySQL Clone Table

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 26, 2023

MySQL Clone Table

 

 

Introduction to MySQL Clone Table

MySQL Clone Table is a feature command that allows copying an existing table to a new one in the same database or for another. This Clone Table query duplicates the present table in a database with its structure design, indexes, default values, constraints, and other characteristics. Copying data records from one current table to a fresh one shows a valuable example of keeping a backup of table data to avoid the situation of table failure or security issues. If we want to perform any query for testing purposes but not hampering the original table, then this clone table is beneficial to create a replica of data.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of MySQL Clone Table

For defining a MySQL Clone Table, we need to apply the succeeding basic syntax structure for writing a query statement in MySQL:

CREATE TABLE NewTable
SELECT ColumnName1, ColumnName2,…
FROM ExistingTable;

For this code above, we will use CREATE TABLE and SELECT statements to copy the data to the newly formed table.

In this syntax, a new table is created initially with the specified name indicated in the statement CREATE TABLE. The result set produced by the SELECT statement defines the structure of this new table. Then, MySQL occupies the new table with the data output from the SELECT query statement.

Now, again we will implement the CREATE TABLE and SELECT statements to clone the table data records to the newly produced table together with the WHERE clause:

CREATE TABLE NewTable
SELECT ColumnName1, ColumnName2,…
FROM ExistingTable WHERE Conditional_Expressions;

Before we create any new table, it is essential to validate whether the table to be formed is already present in the database. For this, the IF NOT EXISTS clause with the CREATE TABLE query statement is required.

Only the main table and its record data are copied from this syntax statement. It will not include other objects of the database like PRIMARY KEY CONSTRAINT, INDEXES, UNIQUE KEY, FOREIGN KEY CONSTRAINTS, TRIGGERS, and so on that is linked with the existing table.

So, we will do this by using the subsequent statement as follows, where the present table, along with all its dependent database objects, will be duplicated:

CREATE TABLE IF NOT EXISTS NewTable LIKE ExistingTable;
INSERT NewTable
SELECT * FROM ExistingTable;

Here, we are required to run two query statements, as shown above. From CREATE TABLE statement, firstly, we will form a new table by cloning the existing table. Next, we will apply the INSERT statement to enter the table data from the existing one into the NewTable.

But, below is the complete query syntax to form a new table by replicating an existing table data to it in another database:

CREATE TABLE Target_Db.NewTableLIKE Source_Db.ExistingTable;
INSERT Target_Db.NewTable
SELECT * FROM Source_Db.ExisitngTable;

Here, Target_Db represents the destination database name, and Source_Db denotes from where we want to copy.

How to Clone a Table in MySQL?

For cloning a table in MySQL, the following steps describe its working of it:

  • Firstly we will build up an empty table based on the existing table definition comprising table structure, indexes, column attributes, etc., using CREATE TABLE query statement.
  • Then, we will fill the empty table by inserting the existing table records with an INSERT statement.
  • For basic replication, copy just the table structure and data of the table of source and not the other database objects using CREATE TABLE and SELECT query as explained in the syntax code above.

Examples of MySQL Clone Table

Given below are the examples mentioned :

Example #1

MySQL Clone Table Example within the same database.

Suppose we have a table named Employees that exists already in the database. Then, we will copy the data from this table to a new table in the same database.

Code:

select * from employees;

Output:

MySQL Clone Table 1

Code:

CREATE TABLE IF NOT EXISTS Employees_Copy
SELECT * FROM Employees;

Here, we have copied the Employees named table data to the newly formed table as Employees_Copy. After executing the code, we need to validate the query in the table Employees_Copy as follows.

Code:

SELECT * FROM employees_Copy;

Output:

MySQL Clone Table 2

On comparing the original and new table, we will see that the new table has only table data, no other column attributes or indexes, and other privileges.

Suppose we want to clone only those data values having EmpSalary greater than 40,000. Then, we must apply the WHERE clause to the SELECT query using the following statement.

Code:

CREATE TABLE IF NOT EXISTS Employees_Salary
SELECT * FROM Employees WHERE EmpSalary> 40000 ;

Now, let us view the contents from the Employees_Salary table as follows.

Code:

SELECT * FROM employees_salary;

Output:

MySQL Clone Table 3

Again, suppose we duplicate the Employees table and all its database objects, including indexes, attributes, keys, etc., related to this table to a new table. In that case, we will implement the following query statement.

Code:

CREATE TABLE Employees_Dup LIKE Employees;
INSERT Employees_Dup
SELECT * FROM Employees;

Let us view the result of Employees_Dup like this.

Code:

SELECT * FROM employees_dup;

Output:

result of Employees_Dup

Example #2

MySQL Clone Table Example in the other database.

We have another database named Empdb, so let us copy the Employees table to a new table generated in this database.

Code:

CREATE TABLE Empdb.Employees_Data LIKE Personsdb.Employees;
INSERT Empdb.Employees_Data
SELECT * FROM Personsdb.Employees;

View the results from the Empdb database.

Code:

SELECT * FROM Empdb.Employees_Data;

Output:

in the other database

Advantages of MySQL Clone Table

Given below are the advantages mentioned:

  • This MySQL Clone Table shows several techniques to replicate an existing table in the database, either within the same or in another server database.
  • Copying a table is significant in taking exact data backups of the database to resist any data failure issues.
  • Also, for testing purposes, to clone the production data records of a table available in the server without causing any effect on the existing table.
  • This clone table command in MySQL thus gives data maintenance and security type of services.

Conclusion

The clone table command in MySQL is responsible for taking a future backup of data records to survive in any failure condition. This also helps to provide the replica and production data to test without involving the main table so that we can check the functionality and data security for the tables within the same database or among others.

Recommended Articles

We hope that this EDUCBA information on “MySQL Clone Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Admin Tool
  2. UNIQUE INDEX in MySQL
  3. MySQL REGEXP
  4. MySQL GROUP_CONCAT()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW