JIYIK CN >

The purpose of this article is to explore different ways to create a copy of a table in MySQL.

The source table is also called the table to be copied, and the target table is called the clone table, which can be from the same or a different database in the MySQL server.

We will explore the following methods of copying tables in MySQL:

• Use CREATE TABLE ... AS SELECTthe statement to clone a table.
• Use WHEREthe clause to clone a table containing partial data.

Before we begin, we'll create a dummy dataset to work with. Here, we create a table, student_details, and 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 and rows containing the first and last names of students. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code will 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's create a students_data_backupcopy of the above table called .

Use CREATE TABLE ... AS SELECTthe statement to clone a table

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

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

student_detailsThe above code creates a copy table named from the table that acts as the source table students_data_backup. We can visualize this new table using the following query students_data_backup:

select * from students_data_backup;

The output of the above code is 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

Use WHEREthe clause to clone a table with partial data

Sometimes, only certain specific parts need to be cloned. We can use WHEREthe clause and CREATE TABLE ... AS SELECTstatement to do this.

Let's try to partially clone student_detailsthe table. We choose stu_idto clone the table using only the first five .

We can do this 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 example 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

student_detailsAs we can see, copies of the tables have been created , but only the first five stu_id.

Using this technique, we can clone part of the data from a specific table as per our requirement. With the help of above two methods, we can easily create a copy of the source table.