How to Copy a Table in MySQL
-
Clone Table Using
CREATE TABLE ... AS SELECT
Statement -
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.