基于 SELECT 结果的 SQL INSERT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5903912/
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
SQL INSERT based on SELECT results
提问by Matt D
Forgive me as I'm relatively new to SQL. But I'm trying to take modified data from a SELECT query on one table, and use it to populate data into another table.
请原谅我，因为我对 SQL 比较陌生。但我试图从一个表上的 SELECT 查询中获取修改后的数据，并使用它将数据填充到另一个表中。
SELECT ID FROM TABLE WHERE VALUE=10
I want to insert the resultant ID's into another Table, but with modifications such that the value is:
我想将结果 ID 插入到另一个表中，但要进行修改，使其值为：
1stString.ID.2ndString
I've found answers for how to use the ID from the SELECT in the insert, but I can't seem to get it working when trying to concatenate. There are also other values that I'm inserting, but they're literals (I'm trying to initialize default key settings for the ID's given in another table.
我已经找到了有关如何在插入中使用 SELECT 中的 ID 的答案，但是在尝试连接时我似乎无法使其正常工作。我还插入了其他值，但它们是文字（我正在尝试为另一个表中给出的 ID 初始化默认键设置。
Using MS SQL Studio 2008, btw.
使用 MS SQL Studio 2008，顺便说一句。
回答by rosscj2533
INSERT INTO table (field)
SELECT '1stString.' + cast(id as varchar(50)) + '.2ndString'
FROM table2
WHERE id = 10
Edit - response to comment: You're on the right track, but you want to select your hard-coded strings from your table, like this:
编辑 - 对评论的回应：您走在正确的轨道上，但您想从表中选择硬编码字符串，如下所示：
INSERT INTO table1 (field1, field2, field3)
SELECT '1stVal', '2ndVal', '1stString.' + cast(id as varchar(50)) + '.2ndString'
FROM table2
WHERE id = 10
This is also illustrated in Dustin Laine's answer.
Dustin Laine 的回答也说明了这一点。
回答by Dustin Laine
INSERT INTO table1
(
f1, f2, f3, f4
)
SELECT f1, f2, f3, 'defaultval' as f4
FROM table2
WHERE value = 1
