如何将数据从一个表复制到 MySQL 中的另一个新表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7482443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy data from one table to another new table in MySQL?
提问by Fero
I want to copy data from one table to another in MySQL.
我想将数据从一个表复制到 MySQL 中的另一个表。
Table 1 (Existing table):
表 1（现有表）：
aid
st_id
from_uid
to_gid
to_uid
created
changed
subject
message
link
Table 2 (New Table)
表 2（新表）
st_id
uid
changed
status
assign_status
I want to copy some fields of data from TABLE 1 into TABLE 2.
我想将表 1 中的一些数据字段复制到表 2 中。
Can this be done using MySQL queries?
这可以使用 MySQL 查询来完成吗？
回答by jdias
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.
如果要包含 table1 中的所有行。否则，如果您只想添加 table1 的子集，则可以在末尾添加 WHERE 语句。
I hope this helps.
我希望这有帮助。
回答by Bryan
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
回答by php
You can easily get data from another table. You have to add fields only you want.
您可以轻松地从另一个表中获取数据。您只需添加您想要的字段。
The mysql query is:
mysql 查询是：
INSERT INTO table_name1(fields you want)
SELECT fields you want FROM table_name2
where, the values are copied from table2 to table1
其中，值从 table2 复制到 table1
回答by Seymur Asadov
CREATE TABLE newTable LIKE oldTable;
Then, to copy the data over
然后，将数据复制过来
INSERT INTO newTable SELECT * FROM oldTable;
回答by dexter.ba
The best option is to use INSERT...SELECT statement in mysql.
最好的选择是在 mysql 中使用 INSERT...SELECT 语句。
回答by mikey
SELECT *
INTO newtable [IN externaldb]
FROM table1;
回答by Nana Partykar
INSERT INTO Table1(Column1,Column2..) SELECT Column1,Column2.. FROM Table2 [WHERE <condition>]
回答by Qanuni
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
回答by Biddut
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
回答by Sriyashree Swain
You should create table2 first.
您应该先创建 table2。
insert into table2(field1,field2,...) select field1,field2,.... from table1 where condition;
