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

Question

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.

If you want to create and copy the content in a single shot, just use the SELECT:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

This worked for me. You can make the SELECT statement more complex, with WHERE and LIMIT clauses.

First duplicate your large table (without the data), run the following query, and then truncate the larger table.

INSERT INTO table_small (SELECT * FROM table_large WHERE column = 'value' LIMIT 100)

Super simple. :-)