May 16, 2021 MariaDB
In some cases, an exact copy of an existing table needs to be generated. C REATE ... S elect statements cannot produce this output because it ignores indexes and default values.
The process of copying a table is as follows -
Use SHOW CREATE TABLE to generate 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.
If you still need to copy table data, use INSERT INTO ... S ELECT statement.
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 way to create a copy uses the CREATE TABLE AS statement. T he statement copies all columns, defines them, and populates the copy with the source table data.
Check its syntax as follows -
CREATE TABLE clone_tbl AS SELECT columns FROM original_tbl WHERE conditions];
See an example of its use as follows -
CREATE TABLE products_copy_tbl AS SELECT * FROM products_tbl;