使用SELECT执行INSERT插入多个记录
DodgyOldTable”和“MainTable关系是1:1。表选项包含在选项描述字段中Val1”,“选项Val2”和“选项Val3记录。我需要从DodgyOldTable选择一个插入MainTable_Option中。像这样的东西:INSERT MainTable_Option (,)SELECT ID,(CASE WHEN OptionVal1 = 'y' THEN (SELECT OptionID FROM Option WHERE OptionDesc = 'OptionVal1') ENDFROM DodgyOldTable如果可能的话,我想避免使用几种不同的方法select句子执行插入操作。
替代文字http://www.freeimagehosting.net/uploads/863f10bf5f.jpg
解决方案:
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’)
页:
[1]