Copying a Table in MySQL

To copy a table in MySQL, issue two commands: CREATE TABLE to create the table and an INSERT statement to copy the data.

To copy a table in MySQL use the CREATE TABLE ... LIKE syntax to build a new table based on an existing definition and indexes.

This CREATE TABLE statement makes a new table SR_REF_FUND_SOURCE_NAME_RX that has the same columns and indexes as SR_REF_FUND_SOURCE_NAME.



CREATE TABLE `etldemos`.SR_REF_FUND_SOURCE_NAME_RX
LIKE `etldemos`.SR_REF_FUND_SOURCE_NAME


This INSERT statement runs a SELECT on the source table and inserts the records into the new target table.  The "*" works because the order of the columns of the two tables is identical.  If this is not the case, add the column definitions to the INSERT and SELECT.

INSERT INTO `etldemos`.SR_REF_FUND_SOURCE_NAME_RX
SELECT * FROM `etldemos`.SR_REF_FUND_SOURCE_NAME