EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Clone Table

MySQL Clone Table

By Aanchal SharmaAanchal Sharma

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.

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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()
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more