MySQL 使用 INSERT 查询将行从一个表复制到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15649792/
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
Copy rows from one table onto another using INSERT query
提问by Sharadha Jayaraman
I have a table with 158 columns and 22,000 rows and I have another empty table where I want insert values dynamically based on the WHERE condition coming from the user. The SELECT query will look something like this:
我有一个包含 158 列和 22,000 行的表，我有另一个空表，我想根据来自用户的 WHERE 条件动态插入值。SELECT 查询将如下所示：
SELECT * FROM mygrist_tables WHERE suic_att>=5 AND gender='M'
SELECT * FROM mygrist_tables WHERE suic_att>=5 AND性别='M'
This gives me about 9,000 records back (say). I want to insert these records into another table (just this filtered data). Is it possible? Could anyone give me an idea of how the INSERT query would look like and whether I need to create another table with all those 158 cloumns or could the INSERT query dynamically create all those 158 columns? Also, could I do this using a view or would a table be essential? Thanks in advance!
这给了我大约 9,000 条记录（比如说）。我想将这些记录插入另一个表（只是这个过滤数据）。是否可以？任何人都可以让我了解 INSERT 查询的外观以及我是否需要使用所有 158 个 cloumns 创建另一个表，或者 INSERT 查询是否可以动态创建所有这些 158 个列？另外，我可以使用视图来做到这一点还是表格是必不可少的？提前致谢！
回答by sgeddes
It sounds like you want to run the above SELECTstatement and INSERTthe results into a new table that does not exist. If so, this should work:
听起来您想将上述SELECT语句和INSERT结果运行到一个不存在的新表中。如果是这样，这应该有效：
SELECT * INTO YourNewTable
FROM mygrist_tables
WHERE suic_att>=5 AND gender='M'
Assuming YourNewTable already existed, then you'd need to run INSERT INTO:
假设 YourNewTable 已经存在，那么你需要运行 INSERT INTO：
INSERT INTO YourNewTable
SELECT *
FROM mygrist_tables
WHERE suic_att>=5 AND gender='M'
Optionally you may need to specify the columns in they are not the same.
或者，您可能需要指定不同的列。
EDIT- Rereading comments and realizing DB is MySQL, to create a new table from a SQL statement, you should use:
编辑- 重读评论并意识到 DB 是 MySQL，要从 SQL 语句创建一个新表，您应该使用：
CREATE TABLE YourNewTable
SELECT *
FROM mygrist_tables
WHERE suic_att>=5 AND gender='M';
回答by rbedger
You can use the SELECT INTO syntax
您可以使用 SELECT INTO 语法
SELECT *
INTO MyNewTable
FROM mygrist_tables WHERE suic_att>=5 AND gender='M'
But you won't be able to insert into a table that already exists like that. If your table already exists, you would use
但是您将无法插入到已经存在的表中。如果您的表已经存在，您将使用
INSERT INTO MyOldTable
([LIST OUT YOUR COLUMNS HERE])
SELECT [LIST OUT YOUR COLUMNS HERE]
FROM mygrist_tables WHERE suic_att>=5 AND gender='M'
