148

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?

Community
  • 1
  • 1
Joneph O.
  • 6,887
  • 5
  • 20
  • 9

8 Answers8

208

If the tables have the same structure:

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

If the tables have different structures:

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

You can also add conditions:

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1
Pikamander2
  • 7,332
  • 3
  • 48
  • 69
ggiroux
  • 6,544
  • 1
  • 22
  • 23
  • What will be happen if someone wants to perform insert operations in source table during running this query ? it locks insert operation or not ? – Lawakush Kurmi Oct 26 '17 at 12:52
167

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
GSto
  • 41,512
  • 37
  • 133
  • 184
  • 1
    I found this code `SELECT * INTO newTable FROM sourceTable` in [w3school](http://www.w3schools.com/sql/sql_select_into.asp), why it is not working in `MySQL` – Kasun Siyambalapitiya Jul 31 '16 at 04:31
  • @KasunSiyambalapitiya `SELECT ... INTO` is meant to export a table to an output file, or to variables; not directly into a table. See http://dev.mysql.com/doc/refman/5.7/en/select-into.html – Doktor J Aug 29 '16 at 17:24
  • @Kasun Siyambalapitiya that w3school page is for a different SQL, not intended for MySQL. w3schools now has error reporting, if you find issues report it on their site to assist with accurate knowledge. – Nightwolf Sep 27 '17 at 10:50
34

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;
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
22

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.

Jason
  • 661
  • 5
  • 17
18

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;
Jesse
  • 750
  • 1
  • 9
  • 25
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 6
    +1 - although the new table won't have the index definitions from the first. The "create ... like ..." approach will copy index definitions too. – Martin Feb 28 '10 at 12:16
2

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

Sandor Fekete
  • 21
  • 2
0
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

jisna
  • 2,225
  • 2
  • 16
  • 23
-2

Try this. Works well in my Oracle 10g,

CREATE TABLE new_table
  AS (SELECT * FROM old_table);
Rizier123
  • 58,877
  • 16
  • 101
  • 156
RAHUL KUMAR
  • 31
  • 1