In MySQL, how to copy the content of one table to another table within the same database?

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

EDIT: or if the tables have different structures you can also:

INSERT INTO TARGET_TABLE (`col1`,`col2`) SELECT `col1`,`col2` FROM SOURCE_TABLE;

EDIT: to constrain this..

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1

If the table doesn't exist, you can create one with the same schema like so:

CREATE TABLE table2 LIKE table1;

Then, to copy the data over:

INSERT INTO table2 SELECT * FROM table1

If table1 is large and you don't want to lock it for the duration of the copy process, you can do a dump-and-load instead:

CREATE TABLE table2 LIKE table1;

SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;
LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table2;

This worked for me,

CREATE TABLE newtable LIKE oldtable;

Replicates newtable with old table

INSERT newtable SELECT * FROM oldtable;

Copies all the row data to new table.

Related