Cloning a table in MySQL or MariaDB is a common task for database administrators and developers. This can be useful for testing purposes, backup, or transferring data from one table to another.
Cloning or copying a table requires creating a new table with the same structure as the source table and copying the data from the source table to the new table.
You can use SQL statements to clone a table in MySQL or MariaDB. The CREATE TABLE statement enables you to create a new table based on the structure of an existing table. The INSERT INTO statement allows you to copy the data from one table to another. You can quickly and easily clone a table in MySQL or MariaDB by combining these statements.
Steps to copy MySQL or MariaDB table:
- Log in to MySQL or MariaDB server using mysql client as administrative user or user with appropriate access.
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.32-0ubuntu0.22.10.2 (Ubuntu) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
- Check existing, source table to confirm.
mysql> SHOW TABLES; +----------------------+ | Tables_in_employees | +----------------------+ | current_dept_emp | | departments | | dept_emp | | dept_emp_latest_date | | dept_manager | | employees | | salaries | | titles | +----------------------+ 8 rows in set (0.01 sec)
- Create a table with same structure as the source table.
mysql> CREATE TABLE employees_new LIKE employees; Query OK, 0 rows affected (0.03 sec)
This will just create a new, empty table and does not copy the followings;
- Foreign key definitions
- DATA DIRECTORY
- INDEX DIRECTORY
- Data
The target table could be within the same database or in a different database. If different, specify the database name in the query as the following:
database_name.table_name
Make sure you have appropriate permissions on the related databases and tables.
- Verify the cloned table.
mysql> DESCRIBE employees_new;
This command will show the structure of the new table, allowing you to confirm its resemblance to the original table.
- Check copied records to verify that the operation was successful.
mysql> SELECT * FROM employees_new; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | ##### snipped | 499997 | 1961-08-03 | Berhard | Lenart | M | 1986-04-21 | | 499998 | 1956-09-05 | Patricia | Breugel | M | 1993-10-13 | | 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 | +--------+------------+------------+-----------+--------+------------+ 300024 rows in set (0.10 sec)

Mohd Shakir Zakaria is a skilled cloud architect with a background in development, entrepreneurship, and open-source advocacy. As the founder of Simplified Guide, he helps others understand the complexities of computing, making tech concepts accessible to all.
Comment anonymously. Login not required.