How to copy data from one table to another new table in MySQL?
MysqlCopyMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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
Solution 3 - Mysql
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
Solution 4 - Mysql
CREATE TABLE newTable LIKE oldTable;
Then, to copy the data over
INSERT INTO newTable SELECT * FROM oldTable;
Solution 5 - Mysql
The best option is to use INSERT...SELECT statement in mysql.
Solution 6 - Mysql
SELECT *
INTO newtable [IN externaldb]
FROM table1;
Solution 7 - Mysql
INSERT INTO Table1(Column1,Column2..) SELECT Column1,Column2.. FROM Table2 [WHERE <condition>]
Solution 8 - Mysql
You should create table2 first.
insert into table2(field1,field2,...) select field1,field2,.... from table1 where condition;
Solution 9 - Mysql
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
Solution 10 - Mysql
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
Solution 11 - Mysql
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;