How to clone a table in MySQL or MariaDB

Cloning a table in MySQL or MariaDB gives you a disposable copy for query testing, schema rehearsal, or one-off bulk changes while the source table remains untouched.

The usual clone sequence creates an empty table first and copies rows afterward. CREATE TABLE … LIKE builds the destination from the source table definition, while INSERT INTO … SELECT loads the data only after the destination name and table structure have been checked.

This is a table-level copy, not a full duplicate of every dependent object. The clone keeps ordinary columns, primary keys, and secondary indexes, but foreign keys and triggers must be recreated separately, and rerunning the data-copy step appends rows instead of refreshing the clone. Generated columns or partial copies also need explicit column lists so the server inserts only writable columns.

1. Open the MySQL or MariaDB client as a user that can read the source table and create the destination table.

   $ mysql --user=root --password
   Enter password:
   Welcome to the MySQL monitor. Commands end with ; or \g.
   mysql>

   Typical privileges include SELECT on the source table plus CREATE and INSERT in the destination schema. On MariaDB hosts, use the mariadb client when that is the installed client name.

2. Select the database that contains the source table.

   mysql> USE company;
   DATABASE changed

3. Confirm the source is a base table in the current database.

   mysql> SHOW FULL TABLES LIKE 'employees';
   +-------------------------------+------------+
   | Tables_in_company (employees) | Table_type |
   +-------------------------------+------------+
   | employees                     | BASE TABLE |
   +-------------------------------+------------+
   1 ROW IN SET (0.00 sec)

   CREATE TABLE … LIKE works with base tables, not views. Use a separate CREATE TABLE … SELECT or dump-and-load workflow when the source object is a view.

4. Confirm the destination table name is unused.

   mysql> SHOW TABLES LIKE 'employees_clone';
   Empty SET (0.00 sec)

   Dropping an existing table to reuse the same name permanently deletes its rows.

5. Create an empty clone from the source table definition.

   mysql> CREATE TABLE employees_clone LIKE employees;
   Query OK, 0 ROWS affected (0.01 sec)

   Stop here if only a structure-only copy is needed.

6. Inspect the cloned definition before copying data.

   mysql> SHOW CREATE TABLE employees_clone\G
   *************************** 1. ROW ***************************
          TABLE: employees_clone
   CREATE TABLE: CREATE TABLE `employees_clone` (
     `id` INT NOT NULL AUTO_INCREMENT,
     `first_name` VARCHAR(100) NOT NULL,
     `last_name` VARCHAR(100) NOT NULL,
     `department_id` INT NOT NULL,
     `hired_on` DATE NOT NULL,
     `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (`id`),
     KEY `idx_department_id` (`department_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   1 ROW IN SET (0.00 sec)

   The final ENGINE, charset, and collation line can differ between MySQL and MariaDB. Recreate foreign keys and triggers separately when the clone must keep the same relationships or trigger-based behavior.

7. Copy the source rows into the cloned table.

   mysql> INSERT INTO employees_clone SELECT * FROM employees;
   Query OK, 3 ROWS affected (0.00 sec)
   Records: 3  Duplicates: 0  Warnings: 0

   If employees_clone already contains rows, this statement appends another copy. For generated columns or custom column mapping, use an explicit writable-column list that names the same writable columns on both sides of INSERT … SELECT.

8. Compare the source and clone row counts.

   mysql> SELECT (SELECT COUNT(*) FROM employees) AS src_rows,
       ->        (SELECT COUNT(*) FROM employees_clone) AS clone_rows;
   +----------+------------+
   | src_rows | clone_rows |
   +----------+------------+
   |        3 |          3 |
   +----------+------------+
   1 ROW IN SET (0.00 sec)

   Cross-schema clones on the same server use fully qualified names, for example CREATE TABLE archive.employees_clone LIKE company.employees; followed by INSERT INTO archive.employees_clone SELECT * FROM company.employees;.

9. Preview rows from the cloned table.

   mysql> SELECT * FROM employees_clone ORDER BY id LIMIT 3;
   +----+------------+-----------+---------------+------------+---------------------+
   | id | first_name | last_name | department_id | hired_on   | created_at          |
   +----+------------+-----------+---------------+------------+---------------------+
   |  1 | Taylor     | Nguyen    |             1 | 2022-03-15 | 2026-06-07 04:40:00 |
   |  2 | Casey      | Morgan    |             2 | 2023-07-20 | 2026-06-07 04:40:00 |
   |  3 | Riley      | Singh     |             1 | 2021-11-02 | 2026-06-07 04:40:00 |
   +----+------------+-----------+---------------+------------+---------------------+
   3 ROWS IN SET (0.00 sec)