Home » MySQL

MySQL Copy Table

MySQL | Copying a Table: Learn how to create a copy/clone/duplicate table of an existing table in MySQL?
Submitted by Apurva Mathur, on September 20, 2022

Copy/Clone/Duplicate Table

A Database is basically a collection of data, we can assume it as a hard copy file but when it comes to manage the data electronically then a database comes into the picture.

As we know, a table is simply used to keep records. Rows and columns are used to keep the record of the entries.

Copying a table feature of MySQL is really a boon to all of us. As if we have deleted any crucial table by mistake then in that case if we have a copy of that table it'll be helpful for us as we can use our copied table. We can copy any table along with all its definition to a temporary file.

What is the need to copy a table?

• Copying a table helps us to retrieve data.
• Act as a BACKUP master feature.
• Increases data security.
• Helps you to try different logical queries without risking the actual table.

Copy Table Syntax

INSERT INTO new_table_name SELECT * FROM old_table_name;

Let us take some examples;

Copy Table Examples

Case 1: Suppose I have a table named "student_details" and I want to copy all my "student_details" data to my new table named "details",

In such case, I will simply write the following query,

INSERT INTO details SELECT * FROM student_details;

Here "details" are my new table name (in which I will copy my all data) and "student_details" is my old table name (From which I will copy all my data).

To run this query it is important to keep the following two points in mind:

• Your new table (in which you want to copy your data) should have the same column names.
• In this case, it is important that you have both the tables (Copied table and old table) in one database only.

Case 2: Suppose, in spite of creating whole new table columns one by one, you want to just copy all your data from one table to another, you can also use the following statement,

CREATE TABLE new_table_name SELECT * FROM old_table_name;

In this case, it is not at all necessary to make a table, we are simply creating and copying all our data in a single hit.

CREATE TABLE backup SELECT * FROM student_details;

As you can see after hitting the query, all our data is copied in the table named "backup".

---