Clone Tables in MySQL

Introduction

Sometimes you may need to create a copy of an existing table in MySQL.

This is known as cloning a table.

Cloning is useful for:

  • Backup purposes

  • Testing and development

  • Creating duplicate data structures

  • Experimenting without affecting the original data

What is Table Cloning

Table cloning means creating a duplicate table from an existing one.

You can clone:

  • Only the table structure

  • Structure + data

MySQL provides multiple ways to do this.

Method 1: Clone Only Structure

To copy only the table structure (no data), use:

CREATE TABLE new_table LIKE old_table;

Example

CREATE TABLE Students_copy LIKE Students;

This creates a new table with:

  • Same columns

  • Same data types

  • Same constraints

But no data is copied.

Method 2: Clone Structure + Data

To copy both structure and data:

INSERT INTO new_table SELECT * FROM old_table;

Example

INSERT INTO Students_copy SELECT * FROM Students;

Now the new table contains all the records from the original table.

Method 3: Clone in One Step

You can also create and copy data in one command:

CREATE TABLE new_table AS SELECT * FROM old_table;

Example

CREATE TABLE Students_backup AS SELECT * FROM Students;

Note:
This copies data but may not copy constraints like PRIMARY KEY or indexes.

Differences Between Methods

MethodStructureDataConstraints
CREATE TABLE LIKEYesNoYes
INSERT INTO SELECT—Yes—
CREATE TABLE AS SELECTYesYesNo

Example Scenario

Suppose you have a table:

Students

You want a backup:

CREATE TABLE Students_backup LIKE Students; INSERT INTO Students_backup SELECT * FROM Students;

When to Clone Tables

  • Creating backup copies

  • Testing queries safely

  • Data migration

  • Debugging without affecting the original data

Common Mistakes

  • Forgetting to copy data after the structure

  • Assuming constraints are copied in all methods

  • Using the wrong table names

  • Overwriting existing tables

Key Points to Remember

  • Cloning creates a duplicate table.

  • Use CREATE TABLE LIKE for structure.

  • Use INSERT INTO SELECT for data.

  • Use CREATE TABLE AS SELECT for a quick copy.

  • Always verify copied data.