INSERT INTO...SELECT 用于所有 MySQL 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5253302/
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
INSERT INTO...SELECT for all MySQL columns
提问by Kyle
I'm trying to move old data from:
我正在尝试从以下位置移动旧数据：
this_table >> this_table_archive
copying all columns over. I've tried this, but it doesn't work:
复制所有列。我试过这个，但它不起作用：
INSERT INTO this_table_archive (*) VALUES (SELECT * FROM this_table WHERE entry_date < '2011-01-01 00:00:00');
Note: the tables are identical and have idset as a primary key.
注意：表是相同的，并已id设置为主键。
回答by Mark Byers
The correct syntax is described in the manual. Try this:
手册中描述了正确的语法。尝试这个：
INSERT INTO this_table_archive (col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM this_table
WHERE entry_date < '2011-01-01 00:00:00';
If the id columns is an auto-increment column and you already have some data in both tables then in some cases you may want to omit the id from the column list and generate new ids instead to avoid insert an id that already exists in the original table. If your target table is empty then this won't be an issue.
如果 id 列是一个自增列，并且您已经在两个表中都有一些数据，那么在某些情况下，您可能希望从列列表中省略 id 并生成新的 id 来避免插入已存在于原始中的 id桌子。如果您的目标表是空的，那么这将不是问题。
回答by RichardTheKiwi
For the syntax, it looks like this (leave out the column list to implicitly mean "all")
对于语法，它看起来像这样（省略列列表以隐式表示“全部”）
INSERT INTO this_table_archive
SELECT *
FROM this_table
WHERE entry_date < '2011-01-01 00:00:00'
For avoiding primary key errors if you already have data in the archive table
如果存档表中已有数据，则用于避免主键错误
INSERT INTO this_table_archive
SELECT t.*
FROM this_table t
LEFT JOIN this_table_archive a on a.id=t.id
WHERE t.entry_date < '2011-01-01 00:00:00'
AND a.id is null # does not yet exist in archive
回答by Pratik
Addition to Mark Byers answer :
除了 Mark Byers 的回答：
Sometimes you also want to insert Hardcodeddetails else there may be Unique constraint fail etc. So use following in such situation where you override some values of the columns.
有时您还想插入硬编码的详细信息，否则可能会出现唯一约束失败等。因此，在您覆盖某些列值的情况下，请使用以下内容。
INSERT INTO matrimony_domain_details (domain, type, logo_path)
SELECT 'www.example.com', type, logo_path
FROM matrimony_domain_details
WHERE id = 367
Here domainvalue is added by me me in Hardcoded way to get rid from Unique constraint.
这里域值是我以硬编码方式添加的，以摆脱唯一约束。
回答by Daniel Casserly
don't you need double () for the values bit? if not try this (although there must be a better way
值位不需要 double () 吗？如果不试试这个（虽然必须有更好的方法
insert into this_table_archive (id, field_1, field_2, field_3)
values
((select id from this_table where entry_date < '2001-01-01'),
((select field_1 from this_table where entry_date < '2001-01-01'),
((select field_2 from this_table where entry_date < '2001-01-01'),
((select field_3 from this_table where entry_date < '2001-01-01'));
回答by Developer
INSERT INTO vendors (
name,
phone,
addressLine1,
addressLine2,
city,
state,
postalCode,
country,
customer_id
)
SELECT
name,
phone,
addressLine1,
addressLine2,
city,
state ,
postalCode,
country,
customer_id
FROM
customers;
