SQL 使用 SELECT 执行 INSERT 以插入多条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/601906/
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
Perform INSERT with SELECT to insert multiple records
提问by Matthew Dresser
In the diagram below there is a 1:1 relationship between 'DodgyOldTable' and 'MainTable'. Table 'Option' contains records with 'OptionVal1', 'OptionVal2' and 'OptionVal3' in the 'OptionDesc' field. I need to do an insert into MainTable_Option with a select from DodgyOldTable. Something like this:
在下图中，“DodgyOldTable”和“MainTable”之间存在 1:1 的关系。表“Option”在“OptionDesc”字段中包含带有“OptionVal1”、“OptionVal2”和“OptionVal3”的记录。我需要使用 DodgyOldTable 中的选择插入 MainTable_Option。像这样的东西：
INSERT MainTable_Option ([MainTableID],[OptionID])
SELECT ID, (CASE WHEN OptionVal1 = 'y' THEN
(SELECT OptionID
FROM Option
WHERE OptionDesc = 'OptionVal1') END
FROM DodgyOldTable
If possible I want to avoid using several different select statements to perform the insert operation.
如果可能，我想避免使用几个不同的 select 语句来执行插入操作。
alt text http://www.freeimagehosting.net/uploads/863f10bf5f.jpg
回答by Tomalak
INSERT
MainTable_Option
(
MainTableID,
OptionID
)
SELECT
d.ID,
o.OptionId
FROM
DodgyOldTable d
INNER JOIN Option o ON
(d.OptionVal1 = 'Y' AND o.OptionDesc = 'OptionVal1') OR
(d.OptionVal2 = 'Y' AND o.OptionDesc = 'OptionVal2') OR
(d.OptionVal3 = 'Y' AND o.OptionDesc = 'OptionVal3')
回答by Lieven Keersmaekers
perhaps not the most efficient solution but by using a union, this should work.
也许不是最有效的解决方案，但通过使用联合，这应该有效。
INSERT MainTable_Option ([MainTableID],[OptionID])
SELECT ID, (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal1')
FROM DodgyOldTable dot
WHERE OptionVal1 = 'y'
UNION SELECT ID, (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal2')
FROM DodgyOldTable dot
WHERE OptionVal2 = 'y'
UNION SELECT ID, (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal3')
FROM DodgyOldTable dot
WHERE OptionVal3 = 'y'
回答by Roel Snetselaar
My experience is it is often more easy and more readable to divide it up into smaller bits. So don't try to do everything in one single query. Especially when you are making migration scripts this should not be an issue.
我的经验是将它分成更小的部分通常更容易和更易读。所以不要试图在一个查询中完成所有事情。特别是当您制作迁移脚本时，这应该不是问题。
Write down the steps, maybe introduce a temporary table, write the scripts to migrate your data and you are good to go!
写下步骤，也许引入一个临时表，编写迁移数据的脚本，你就可以开始了！
回答by Maksym Gontar
What about CROSS JOIN solution?
CROSS JOIN 解决方案怎么样？
DECLARE @DodgyOldTable TABLE (ID INT, OptionVal1 CHAR, OptionVal2 CHAR,
OptionVal3 CHAR)
INSERT INTO @DodgyOldTable
SELECT 1, 'y', 'n', 'y' UNION
SELECT 2, 'y', 'n', 'n' UNION
SELECT 3, 'n', 'n', 'y' UNION
SELECT 4, 'y', 'y', 'y' UNION
SELECT 5, 'n', 'n', 'n'
DECLARE @Option TABLE (OptionID INT, OptionDesc VARCHAR(100))
INSERT INTO @Option
SELECT 1, 'OptionVal1' UNION
SELECT 2, 'OptionVal2' UNION
SELECT 3, 'OptionVal3'
SELECT ID, OptionID FROM
(
SELECT
ID,
CASE
WHEN (OptionVal1 = 'y' AND OptionDesc = 'OptionVal1')
OR (OptionVal2 = 'y' AND OptionDesc = 'OptionVal2')
OR (OptionVal3 = 'y' AND OptionDesc = 'OptionVal3')
THEN OptionID
ELSE NULL
END AS OptionID
FROM @DodgyOldTable DOT CROSS JOIN @Option O
)CRS
WHERE OptionID IS NOT NULL
回答by Juan Kev
11 Yrs later but might help someone:
11 年后，但可能会帮助某人：
INSERT INTO schema.tableName (col1, col2,col3, col4, col5, col6, col7)
SELECT col1,
col2,
'Static value',
CURRENT_TIMESTAMP,
'Anothe static value',
1,
(SELECT col7 FROM schema2.anotherTableName)
FROM schema.tableX;
col1 & col2 come from the same select
col3, col5 are static strings
col4 is the current system time
col6 a Num value,
col7 from a select that returns one value.
col1 & col2 来自同一个选择
col3, col5 是静态字符串
col4 是当前系统时间
col6 Num 值，
col7 来自返回一个值的选择。
NB: Ensure the select with multiple values comes at the beginning. This is MySQL
注意：确保具有多个值的选择位于开头。这是 MySQL
回答by Rich Andrews
You could UNION all of selects together to give one result set but it depends on your reasons for not wanting the multiple selects - if there are too many or the number of selects may change frequently it'll still be a pain to amend the query with the additional selects. Unfortunatly I think you will have to put the logic somewhere that determines which bit(s) of DodgyOldTable map to the new structure and either write a migration script (or SSIS package) to bulk migrate (if this is a one off job) or UNION your results together...
您可以将所有选择联合在一起以提供一个结果集，但这取决于您不想要多个选择的原因 - 如果选择过多或选择数量可能经常变化，那么修改查询仍然很痛苦额外的选择。不幸的是，我认为您必须将逻辑放在某处，以确定 DodgyOldTable 的哪些位映射到新结构，并编写迁移脚本（或 SSIS 包）以进行批量迁移（如果这是一次性工作）或 UNION你们在一起的结果...
INSERT MainTable_Option ([MainTableID],[OptionID])
SELECT ID, (CASE WHEN OptionVal1 = 'y' THEN (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal1') END
FROM DodgyOldTable
WHERE OptionVal1 = 'y
UNION
SELECT ID, (CASE WHEN OptionVal2 = 'y' THEN (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal2') END
FROM DodgyOldTable
WHERE OptionVal2 = 'y
...
回答by thr
I'd say that a manual migration script would be easier to use then trying to do it in a single sql query, if that is an option.
我会说手动迁移脚本会更容易使用，然后尝试在单个 sql 查询中执行它，如果这是一个选项。
