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

I am new to MySQL. I would like to copy the content of one table to another table within the same database. Basically, I would like to insert to a table from another table. Is there easy way of doing this?

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 you want to create and copy the content in a single shot, just use the SELECT:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

Try this. Works well in my Oracle 10g,

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

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.

Thank you

CREATE TABLE target_table SELECT * FROM source_table;

It just create a new table with same structure as of source table and also copy all rows from source_table into target_table.

CREATE TABLE target_table SELECT * FROM source_table WHERE condition;

If you need some rows to be copied into target_table, then apply a condition inside where clause

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;

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

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. :-)