How to Copy a Table in MySQL

1. Clone Table Using CREATE TABLE ... AS SELECT Statement
2. Clone Table With Partial Data Using the WHERE Clause

This tutorial aims to explore different methods to create a copy of a table in MySQL.

The source table is also known as the table to be copied, and the destination table, known as the clone table, can be from the same or separate databases within a MySQL server.

We will explore the following methods to copy a table in MySQL:

• Clone table using CREATE TABLE ... AS SELECT statement.
• Clone table with partial data using the WHERE clause.

Before we begin, we will create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The aforementioned code would give the following result:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now, let us create a copy of the above table with the name students_data_backup.

Clone Table Using CREATE TABLE ... AS SELECT Statement

One of the most basic methods to create a table copy is using the CREATE TABLE ... AS SELECT statement. We can do this operation with the following code:

-- Basic copy table creation
create table students_data_backup as select * from student_details;

The aforementioned code creates a copy table named students_data_backup from the student_details table that acts a the source table. We can visualize this new table students_data_backup with the following query:

select * from students_data_backup;

The output of the aforementioned code results in a temporary table as follows:

stu_id	stu_firstName	stu_lastName
1	        Preet	      Sanghavi
2	        Rich	      John
3	        Veron	      Brow
4	        Geo	          Jos
5	        Hash	      Shah
6			Sachin		  Parker
7			David	      Miller

Clone Table With Partial Data Using the WHERE Clause

Sometimes, only a certain part of a particular needs to be cloned. We can use the WHERE clause and the CREATE TABLE ... AS SELECT statement to perform this operation.

Let us try to clone the student_details table partially. We choose to clone the table with data of only the first five stu_id.

We can do this operation with the following code:

-- Cloning the student_details table with where clause
create table students_data_backup as select * from student_details WHERE stu_id <= 5;

The given code sample will produce the following output:

stu_id	stu_firstName	stu_lastName
1	        Preet	      Sanghavi
2	        Rich	      John
3	        Veron	      Brow
4	        Geo	          Jos
5	        Hash	      Shah

As we can see, a copy of the student_details table has been created but only with the first five stu_id.

Using this technique, we can clone partial data from a particular table depending on our requirement. With the help of the above two methods, we can easily create a copy of the source table.