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

The procedure for a duplicating a table is as follows −

• Utilize 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 duplicate 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;