Nov 1, 2018

How to Copy Table in MySQL

To copy data from a table to a new table, you use CREATE TABLE and SELECT statements as follows:
CREATE TABLE new_table 
SELECT col1, col2, col3 
FROM
    existing_table
WHERE
    conditions;
Note that the statement above just copies the table and its data. It does NOT copy other database objects such as indexes, primary key constraint, foreign key constraints, triggers, etc., associated with the table.


To copy data from one table and also all the dependent objects of the table, you use the following statements:
CREATE TABLE IF NOT EXISTS new_table LIKE existing_table;
 
INSERT new_table
SELECT * FROM existing_table;