MariaDB - Table Cloning

Some situations require producing an exact copy of an existing table. The CREATE...SELECT statement cannot produce this output because it neglects things pke indexes and default values.

The procedure for a duppcating a table is as follows −

Utipze SHOW CREATE TABLE to produce a CREATE TABLE statement that details the entire structure of the source table.

Edit the statement to give the table a new name, and execute it.

Use an INSERT INTO...SELECT statement if you also need the table data copied.

mysql> INSERT INTO inventory_copy_tbl (
   product_id,product_name,product_manufacturer,ship_date)
   
   SELECT product_id,product_name,product_manufacturer,ship_date,
   FROM inventory_tbl;

Another method for creating a duppcate uses a CREATE TABLE AS statement. The statement copies all columns, column definitions, and populates the copy with the source table s data.

Review its syntax given below −

CREATE TABLE clone_tbl AS
   SELECT columns
   FROM original_tbl
   WHERE conditions];

Review an example of its use below −

CREATE TABLE products_copy_tbl AS
   SELECT *
   FROM products_tbl;