How to Copy Table in MySQL

In this tutorial, you will learn how to copy a table in MySQL. Copying a table is one of the most common database operations because it allows you to create a duplicate of an existing table.

You can copy a table to create a backup, test new queries, migrate data, or preserve important information before making changes to the original table.

MySQL provides several methods to copy a table, depending on whether you want to copy only the table structure, only the table data, or both the structure and data.

What is Copy Table in MySQL?

A copy table in MySQL refers to the process of creating a new table from an existing table. Depending on your requirements, the new table can contain:

• Only the table structure (schema)
• Only the table data
• Both the table structure and data

The copied table is completely independent of the original table. This means that any changes made to the original table do not affect the copied table, and any changes made to the copied table do not affect the original table.

Basic Syntax to Copy a Table in MySQL

MySQL provides different syntax to copy a table based on what you want to duplicate. You can copy only the table structure, copy both the structure and data, or create the structure first and then insert the data separately.

The following are the most commonly used methods to copy a table in MySQL.

1. Copy Only the Table Structure

To create a new table with the same structure as an existing table, use the CREATE TABLE … LIKE statement. The general syntax is:

CREATE TABLE new_table
LIKE existing_table;

In this syntax, the LIKE clause creates a new table by copying the structure of an existing table. It copies almost all table definitions, including the following:

• Column names
• Data types
• Column sizes
• NULL and NOT NULL constraints
• Default values
• Primary key
• Unique key
• Indexes
• AUTO_INCREMENT attribute

However, this statement does not copy the table data. The new table is created as an empty table with the same structure as the original table.

When to Use This Method?

You can use this method when you need:

• An empty table with the same structure.
• A backup of the table structure.
• A template for storing new data.
• A testing table without existing records.

2. Copy Both the Table Structure and Data

To create a new table and copy data from an existing table in a single step, use the CREATE TABLE … AS SELECT (often abbreviated as CTAS) statement. The general syntax is:

CREATE TABLE new_table AS
SELECT *
FROM existing_table;

This statement creates a new table and immediately copies all rows from the existing table into it.

The CREATE TABLE statement creates the new table, while the SELECT * statement retrieves all columns and rows from the existing table and inserts them into the new table.

The new table is created based on the result of the SELECT query. Therefore, it contains the same column names, compatible data types, and all the data from the original table.

However, this method does not create an exact copy of the original table because it does not preserve many table attributes. For example, it does not copy the following:

• Primary key
• Foreign keys
• Indexes
• AUTO_INCREMENT property
• CHECK constraints
• Triggers
• Table comments
• Generated columns (if applicable)

As a result, the new table is suitable for storing the copied data, but it is not an exact duplicate of the original table’s schema.

When to Use This Method

You should use this method when you need:

• A quick copy of a table with all its data.
• A reporting table for analysis.
• A temporary working table for testing or data processing.

3. Copy the Structure First, Then Copy the Data

You can also copy a table in two separate steps. First, create the table structure, and then insert all records into it. The general syntax is given below.

CREATE TABLE new_table
LIKE existing_table;

INSERT INTO new_table
SELECT *
FROM existing_table;

In the first step, the CREATE TABLE … LIKE statement creates a new table by copying the structure of the existing table. In the second step, the INSERT INTO … SELECT statement copies all rows from the existing table into the new table.

Since the table structure is created using the LIKE clause, the new table retains almost all important properties, such as:

• Column definitions
• Data types
• NULL and NOT NULL constraints
• Default values
• Primary key
• Unique keys
• Indexes
• AUTO_INCREMENT attribute (the counter is reset for the new table)

After the table is created, the INSERT INTO … SELECT statement copies all data from the original table into the new table.

This method creates a nearly identical copy of the original table. However, depending on your MySQL version and storage engine, some database objects may not be copied automatically, including:

• Foreign key constraints
• Triggers
• Table-level options (such as comments and certain storage settings)

If your application depends on these objects, you must recreate them manually.

When to Use This Method

You should use this method when you need to:

• Create a nearly identical copy of a table.
• Create a complete backup before modifying data.
• Set up a testing environment with the same structure and data.
• Preserve indexes, primary keys, and other important table properties while copying data.

Create a Table in MySQL

Before learning how to copy a table in MySQL, let’s create a table in the MySQL database that will be used throughout this tutorial. We will create a table named Student and insert a few records into it. Then, we will use this table to demonstrate different methods of copying a table.

Step 1: Create the Student Table

Execute the following CREATE TABLE statement to create a table named Student.

CREATE TABLE Student
(
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50),
    course VARCHAR(30),
    fees DECIMAL(8,2)
);

This statement creates a table named Student with four columns.

• student_id stores the unique ID of each student. It is defined as the PRIMARY KEY, and the AUTO_INCREMENT attribute automatically generates a new unique value whenever a new record is inserted.
• student_name stores the name of the student. It can contain up to 50 characters.
• course stores the name of the course in which the student is enrolled. It can contain up to 30 characters.
• fees stores the course fee. The DECIMAL(8,2) data type allows values with up to 8 digits, including 2 digits after the decimal point.

Step 2: Insert Records

After creating the table, insert some records using the following INSERT statement.

INSERT INTO Student(student_name, course, fees)
VALUES
('Deepak', 'Java', 12000),
('Priya', 'Python', 15000),
('Saanvi', 'MySQL', 10000);

This statement inserts three rows into the Student table. Note that the student_id column is not included in the INSERT statement because it uses the AUTO_INCREMENT attribute. MySQL automatically assigns a unique ID to each new record.

Step 3: View the Table Data

After inserting the records, the Student table contains the following data.

Example 1: Copy Only the Table Structure

Let’s create a new table with the same structure as an existing table named Student but without copying its data. We will use the CREATE TABLE … LIKE statement for it.

CREATE TABLE Student_Backup
LIKE Student;

In this example, the LIKE clause creates a new table named Student_Backup by copying the structure of the Student table. However, it does not copy any data from the original table. Therefore, the newly created table is empty.

Verify the Result

Execute the following query to check the contents of the new table.

SELECT * FROM Student_Backup;

Output:

Empty set

The SQL query returns “Empty set” because the table Student_Backup contains no rows. Only the table structure has been copied from the Student table. You can now insert new records into the Student_Backup table without affecting the original Student table.

Example 2: Copy the Table Structure and Data Together

Let’s create a new table and copy all records from an existing table named Student by using a single statement, CREATE TABLE … AS SELECT.

CREATE TABLE Student_Copy AS
SELECT *
FROM Student;

In this example, the CREATE TABLE statement creates a new table named Student_Copy. The SELECT * statement retrieves all columns and rows from the Student table and inserts them into the new table.

The new table is created automatically based on the result of the SELECT query. Therefore, it contains the same columns and all the data from the original table.

Unlike the CREATE TABLE … LIKE statement, this method does not preserve all structural properties of the original table.

Verify the Result

Execute the following SQL query to view the copied data.

SELECT * FROM Student_Copy;

Output:

The output shows that all records from the Student table have been copied to the Student_Copy table. The values in every column are identical to those in the original table.

However, Student_Copy does not have the primary key, indexes, or other constraints that existed in the Student table. It is simply a new table containing the copied data.

Create a Nearly Identical Copy of a Table in MySQL

Let’s create a nearly identical copy of a table in MySQL in which we will copy both the table structure and all its data. We will use the CREATE TABLE … LIKE statement followed by the INSERT INTO … SELECT statement.

Step 1: Copy the Table Structure

CREATE TABLE Student_Clone
LIKE Student;

The CREATE TABLE … LIKE statement creates a new table named Student_Clone with the same structure as the Student table. It copies the following structural elements:

• Column definitions
• Data types
• Column sizes
• NULL and NOT NULL constraints
• Default values
• Primary key
• Unique keys
• Indexes
• AUTO_INCREMENT attribute (the counter starts fresh for the new table)

At this stage, the new table is empty because no data has been copied.

Step 2: Copy the Data

INSERT INTO Student_Clone
SELECT *
FROM Student;

The INSERT INTO … SELECT statement copies all rows from the Student table into the Student_Clone table. After executing both statements, the new table contains the same records as the original table.

The combination of CREATE TABLE … LIKE and INSERT INTO … SELECT is the preferred method when you want to preserve most of the original table’s structure along with all its data. It creates a nearly identical copy of the original table in MySQL.

This method does not always create an exact copy. This is because objects such as triggers, some foreign key definitions, and certain table options may need to be recreated manually.

How to Copy Selected Columns in MySQL

Sometimes, you may not need to copy every column from an existing table. Instead, you may want to create a new table that contains only specific columns. In such cases, use the CREATE TABLE … AS SELECT statement and specify only the required columns in the SELECT clause.

CREATE TABLE Student_Names AS
SELECT student_name, course
FROM Student;

In this SQL query:

• The CREATE TABLE statement creates a new table named Student_Names.
• The SELECT statement retrieves only the student_name and course columns from the Student table.
• Since only these two columns are selected, the new table contains only those columns.
• The student_id and fees columns are not included in the new table.

Output:

How to Copy Selected Rows in MySQL?

Sometimes, you may need to copy only specific records from a table instead of copying all the rows. You can do this by adding a WHERE clause to the SELECT statement. The WHERE clause filters the data so that only the rows matching the specified condition are copied into the new table.

CREATE TABLE Python_Students AS
SELECT *
FROM Student
WHERE course = 'Python';

In this SQL query:

• The CREATE TABLE statement creates a new table named Python_Students.
• The SELECT * statement retrieves all columns from the Student table, while the WHERE clause filters the records to include only students whose course is ‘Python’.
• As a result, the new table contains only the rows that satisfy the specified condition.

Output:

The output shows that only one record has been copied to the Python_Students table. All other records are excluded since they do not satisfy the condition specified in the WHERE clause.

Copy Data from Another Database

MySQL also allows you to copy a table from one database to another on the same MySQL server. This is useful when you want to create a backup, migrate data, or share information between databases.

Suppose you have the following two databases:

• SchoolDB – Contains the original Student table.
• BackupDB – The destination database where you want to create a copy of the table.

Step 1: Copy the Table Structure

First, create the table in the destination database by copying the structure of the original table.

CREATE TABLE BackupDB.Student
LIKE SchoolDB.Student;

This statement creates a new table named Student in the BackupDB database by copying the structure of the Student table from the SchoolDB database. At this stage, the table exists in the BackupDB database, but it contains no data.

Step 2: Copy the Records

After creating the table structure, copy all records from the source table into the destination table.

INSERT INTO BackupDB.Student
SELECT *
FROM SchoolDB.Student;

The INSERT INTO statement inserts rows into the BackupDB.Student table. The SELECT * statement retrieves all rows and columns from the SchoolDB.Student table.

MySQL then inserts the retrieved records into the destination table. After this statement executes successfully, both tables contain the same data.

Note: Both databases (SchoolDB and BackupDB) must exist on the same MySQL server. If the databases are on different MySQL servers, you cannot use this method directly.