如何将多任务风险表合并至Task_Risks并关联Tasks表ID？

错误原因 #

你的SQL报错"invalid table name"的核心问题：

• 作为JOIN对象的子查询未指定别名，SQL引擎无法识别这个临时表；
• 误用FULL JOIN：你需要给每张风险表的所有记录绑定固定的TaskID，完全不需要全连接，全连接在这里会导致逻辑冗余甚至错误。

修正方案1（规范关联写法） #

给子查询添加别名，改用CROSS JOIN（每个任务ID是唯一值，和风险表交叉连接会给每条风险记录带上对应TaskID）：

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
SELECT r.RiskName, r.Mitigation, r.RiskLevel, t.ID AS TaskID
FROM Risks_For_Task_1 r
CROSS JOIN (SELECT ID FROM Tasks WHERE TaskName='Task#1') t
UNION ALL
SELECT r.RiskName, r.Mitigation, r.RiskLevel, t.ID AS TaskID
FROM Risks_For_Task_2 r
CROSS JOIN (SELECT ID FROM Tasks WHERE TaskName='Task#2') t
UNION ALL
SELECT r.RiskName, r.Mitigation, r.RiskLevel, t.ID AS TaskID
FROM Risks_For_Task_3 r
CROSS JOIN (SELECT ID FROM Tasks WHERE TaskName='Task#3') t;

修正方案2（简洁写法） #

如果确认每个TaskName对应唯一ID，可以直接把TaskID作为常量字段查询，避免JOIN操作：

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
SELECT RiskName, Mitigation, RiskLevel, (SELECT ID FROM Tasks WHERE TaskName='Task#1') AS TaskID
FROM Risks_For_Task_1
UNION ALL
SELECT RiskName, Mitigation, RiskLevel, (SELECT ID FROM Tasks WHERE TaskName='Task#2') AS TaskID
FROM Risks_For_Task_2
UNION ALL
SELECT RiskName, Mitigation, RiskLevel, (SELECT ID FROM Tasks WHERE TaskName='Task#3') AS TaskID
FROM Risks_For_Task_3;

说明 #

两种方案都能实现需求：

• 方案1更适合后续可能扩展的场景（比如TaskName对应多个ID的情况）；
• 方案2代码更简洁，性能更优，因为每个子查询仅执行一次。

内容的提问来源于stack exchange，提问作者User104163