mysql :: 插入到表中，数据来自另一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4241621/
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
mysql :: insert into table, data from another table?
提问by Hailwood
I was wondering if there is a way to do this purely in sql:
我想知道是否有办法完全在 sql 中做到这一点：
q1 = SELECT campaign_id, from_number, received_msg, date_received
FROM `received_txts` WHERE `campaign_id` = '8';
INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)
VALUES(q1.campaign_id, q1.from_number, q1.received_msg, q1.date_received);
Note: q1 would return about 30k rows.
注意：q1 将返回大约 30k 行。
Is there any way to do what I am attempting above in straight sql? To just pull the data straight from one table (basically a raw data table) and insert into another table (basically a processed data table)?
有什么办法可以在直接 sql 中做我上面尝试的操作吗？直接从一个表（基本上是原始数据表）中提取数据并插入到另一个表（基本上是处理过的数据表）中？
回答by zerkms
INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)
SELECT campaign_id, from_number, received_msg, date_received
FROM `received_txts`
WHERE `campaign_id` = '8'
回答by Lokesh Deshmukh
for whole row
整排
insert into xyz select * from xyz2 where id="1";
for selected column
对于选定的列
insert into xyz(t_id,v_id,f_name) select t_id,v_id,f_name from xyz2 where id="1";
回答by IamMHussain
Answered by zerkmsis the correct method. But, if someone looking to insert more extra column in the table then you can get it from the following:
zerkms回答的是正确的方法。但是，如果有人希望在表中插入更多额外的列，那么您可以从以下位置获取它：
INSERT INTO action_2_members (`campaign_id`, `mobile`, `email`, `vote`, `vote_date`, `current_time`)
SELECT `campaign_id`, `from_number`, '[email protected]', `received_msg`, `date_received`, 1502309889 FROM `received_txts` WHERE `campaign_id` = '8'
In the above query, there are 2 extra columns named email& current_time.
在上面的查询中，有 2 个名为email& current_time 的额外列。
回答by Mher Arsh
INSERT INTO Table1 SELECT * FROM Table2
回答by Amit Jain
INSERT INTO preliminary_image (style_id,pre_image_status,file_extension,reviewer_id,
uploader_id,is_deleted,last_updated)
SELECT '4827499',pre_image_status,file_extension,reviewer_id,
uploader_id,'0',last_updated FROM preliminary_image WHERE style_id=4827488
Analysis
分析
We can use above query if we want to copy data from one table to another table in mysql
如果我们想将数据从一个表复制到 mysql 中的另一个表，我们可以使用上面的查询
- Here source and destination table are same, we can use different tables also.
- Few columns we are not copying like style_idand is_deletedso we selected them hard coded from another table
- Table we used in source also contains auto increment field so we left that column and it get inserted automatically with execution of query.
- 这里源表和目标表是相同的，我们也可以使用不同的表。
- 很少有列我们不像style_id和is_deleted那样复制，所以我们从另一个表中硬编码选择了它们
- 我们在 source 中使用的表也包含自动增量字段，所以我们离开了该列，它会随着查询的执行自动插入。
Execution results
执行结果
1 queries executed, 1 success, 0 errors, 0 warnings
执行了 1 个查询，1 个成功，0 个错误，0 个警告
Query: insert into preliminary_image (style_id,pre_image_status,file_extension,reviewer_id,uploader_id,is_deleted,last_updated) select ...
查询：插入到previous_image (style_id,pre_image_status,file_extension,reviewer_id,uploader_id,is_deleted,last_updated) select ...
5 row(s) affected
5 行受影响
Execution Time : 0.385 sec Transfer Time : 0 sec Total Time : 0.386 sec
执行时间：0.385 秒传输时间：0 秒总时间：0.386 秒
