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.
$ 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>
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)
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;
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.
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)
Comment anonymously. Login not required.