How to copy data from one table to another new table in MySQL?

Question

I want to copy data from one table to another in MySQL.

Table 1 (Existing table):

aid    
st_id
from_uid
to_gid
to_uid
created
changed
subject
message
link

Table 2 (New Table)

st_id
uid
changed
status
assign_status

I want to copy some fields of data from TABLE 1 into TABLE 2.

Can this be done using MySQL queries?

@jdias To clarify, if it's not a one time job, what should a MySQL noob look into doing instead? — Seanny123, Jul 10 '13 at 08:04
Possible duplicate of [Copy data into another table](https://stackoverflow.com/questions/13237623/copy-data-into-another-table) — Marcus Vinicius Melo, Sep 27 '18 at 17:53

score 339 · Accepted Answer · edited Jan 27 '22 at 12:54

339

This will do what you want:

INSERT INTO table2 (st_id,uid,changed,status,assign_status)
SELECT st_id,from_uid,now(),'Pending','Assigned'
FROM table1

If you want to include all rows from table1. Otherwise you can add a WHERE statement to the end if you want to add only a subset of table1.

edited Jan 27 '22 at 12:54

Phil Dukhov

67,741
15
184
220

answered Sep 20 '11 at 09:45

jdias

5,572
4
22
25

Great, glad to see this is the same as what I've been used to in T-SQL. – jpierson Jan 21 '16 at 14:20
If You want to copy all table1 data then you must create new table2 before copy @SANDEEP – Sachin from Pune Sep 23 '17 at 13:37

score 102 · Answer 2 · answered Jul 27 '14 at 17:09

102

If you don't want to list the fields, and the structure of the tables is the same, you can do:

INSERT INTO `table2` SELECT * FROM `table1`;

or if you want to create a new table with the same structure:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

Reference for insert select; Reference for create table select

answered Jul 27 '14 at 17:09

Bryan

11,398
3
53
78

3

why `[AS]` is in square brackets, what is done by `AS` in here – Kasun Siyambalapitiya Jul 31 '16 at 04:58
3

It denotes that the word is optional. It is written that way because I copied and pasted from the docs; it does not add anything. I think the `AS` is mandatory in other SQL dialects. – Bryan Aug 01 '16 at 11:04
The bracket should be removed for it to perform the task – umekalu Jul 03 '21 at 17:27

score 22 · Answer 3 · edited Aug 24 '14 at 18:42

22

You can easily get data from another table. You have to add fields only you want.

The mysql query is:

INSERT INTO table_name1(fields you want)
  SELECT fields you want FROM table_name2

where, the values are copied from table2 to table1

edited Aug 24 '14 at 18:42

Brad Larson

170,088
45
397
571

answered Dec 13 '13 at 06:07

php

4,307
1
24
13

score 17 · Answer 4 · edited May 23 '17 at 21:08

17

CREATE TABLE newTable LIKE oldTable;

Then, to copy the data over

INSERT INTO newTable SELECT * FROM oldTable;

edited May 23 '17 at 21:08

slfan

8,950
115
65
78

answered May 23 '17 at 20:51

Seymur Asadov

612
5
19

score 4 · Answer 5 · answered Sep 20 '11 at 09:14

4

The best option is to use INSERT...SELECT statement in mysql.

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

answered Sep 20 '11 at 09:14

dexter.ba

292
2
5

1

Intuitive :) I like it. It is a normal SELECT statement, preceded with one line of "INSERT INTO". – Haitham Sweilem Jan 12 '16 at 14:17

score 3 · Answer 6 · answered Nov 12 '15 at 15:15

3

INSERT INTO Table1(Column1,Column2..) SELECT Column1,Column2.. FROM Table2 [WHERE <condition>]

answered Nov 12 '15 at 15:15

Nana Partykar

10,556
10
48
77

3

While this code may answer the question, it would be better to explain how it solves the problem and why to use it. Code-only answers are not useful in the long run. – Tobias Liefke Nov 12 '15 at 15:59

score 3 · Answer 7 · edited Jan 11 '14 at 19:29

3

SELECT *
INTO newtable [IN externaldb]
FROM table1;

http://www.w3schools.com/sql/sql_select_into.asp

edited Jan 11 '14 at 19:29

Eric Leschinski

146,994
96
417
335

answered Sep 20 '11 at 08:57

mikey

2,022
1
13
6

1

Doesn't work. Per [the docs](http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html), "MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension" – Bryan Jul 27 '14 at 17:00

score 2 · Answer 8 · answered Nov 14 '18 at 15:55

2

You should create table2 first.

insert into table2(field1,field2,...)
select field1,field2,....
from table1
where condition;

answered Nov 14 '18 at 15:55

Sriyashree Swain

111
11

score 0 · Answer 9 · edited Feb 17 '18 at 14:27

0

the above query only works if we have created clients table with matching columns of the customer

INSERT INTO clients(c_id,name,address)SELECT c_id,name,address FROM customer

edited Feb 17 '18 at 14:27

Biddut

418
1
6
17

answered May 13 '16 at 06:42

Qanuni

1
1

score 0 · Answer 10 · answered Feb 17 '18 at 12:53

0

You can try this code

insert into #temp 
select Product_ID,Max(Grand_Total) AS 'Sales_Amt', Max(Rec_Amount) ,'',''
from Table_Name group by Id

answered Feb 17 '18 at 12:53

Biddut

418
1
6
17

score 0 · Answer 11 · answered Apr 23 '19 at 07:50

0

IF the table is existed. you can try insert into table_name select * from old_tale;

IF the table is not existed. you should try create table table_name like old_table; insert into table_name select * from old_tale;

answered Apr 23 '19 at 07:50

Jac Tian

15
1

Muhammad Zahid · Answer 12 · 2022-07-28T12:28:13.000

0

$sql_query= "REPLACE INTO customerdeletelist SELECT * FROM customerdetail WHERE account_number = '$account_number' && customer_number = '$customer_number' && updateDate = '$updateDate'";

$shift= mysqli_query($con, $sql_query);

edited Jul 28 '22 at 12:28

answered Jul 07 '22 at 12:45

Muhammad Zahid

1
1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 08 '22 at 00:23

score 0 · Answer 13 · answered Oct 19 '22 at 19:26

0

If someone doing it regularly - u can try to use this tool. Unstable, but ready https://github.com/morkva-vladyslav/database-donor

answered Oct 19 '22 at 19:26

Anonizmus User

1
1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/32984250) – theking2 Oct 24 '22 at 11:44

How to copy data from one table to another new table in MySQL?

13 Answers13

Linked

Related