SQL批量插入任务后关联插入角色数据的实现问题求助

我明白你的困扰——当批量插入Task时，一次性的INSERT语句没办法直接拿到每个新生成的TaskID，也就没法和对应的角色信息绑定插入到TaskRoles表。这里可以用SQL Server的OUTPUT子句来解决这个问题，它能帮你捕获插入后的自增ID，同时保留和原XML任务节点的关联关系。

具体实现步骤： #

1. 创建表变量存储TaskID与原任务节点的映射
   我们需要一个临时存储来记录每个新生成的TaskID对应的原XML任务节点，这样后续才能找到该任务对应的角色列表。

2. 插入Task时用OUTPUT捕获ID和关联信息
   在插入Task的语句中，通过OUTPUT把新生成的TaskID和原XML的任务节点（或者节点中的唯一标识）存入表变量。

3. 关联表变量与原XML插入TaskRoles
   利用表变量里的TaskID和原XML的角色节点关联，批量插入TaskRoles数据。

修改后的完整SQL代码： #

DECLARE @requestID INT;
DECLARE @taskMappings TABLE (TaskID INT, TaskNode XML); -- 存储TaskID和对应的XML任务节点

-- 1. 插入核心请求记录并获取RequestID
INSERT INTO esas.Request (Requestor, Justification, CreatedBy, DateCreated)
SELECT @requestor, @justification, @creator, GETUTCDATE();
SET @requestID = SCOPE_IDENTITY();

-- 2. 插入Task并捕获TaskID与原节点的映射
INSERT INTO esas.Task (RequestID, ToolID, QID, Action)
OUTPUT inserted.TaskID, ParamValues.x1 INTO @taskMappings(TaskID, TaskNode)
SELECT 
    @requestID,
    ParamValues.x1.value('tool[1]', 'INT'),
    ParamValues.x1.value('user[1]', 'VARCHAR(10)'),
    ParamValues.x1.value('action[1]', 'INT') -- 修正原代码中多余的右括号
FROM @tasks.nodes('/request/task') AS ParamValues(x1);

-- 3. 关联映射表与原XML，插入TaskRoles
INSERT INTO esas.TaskRoles (TaskID, RoleID, ActionID)
SELECT 
    tm.TaskID,
    RoleNodes.x2.value('roleID[1]', 'INT'),
    RoleNodes.x2.value('action[1]', 'INT')
FROM @taskMappings tm
CROSS APPLY tm.TaskNode.nodes('task/roles/role') AS RoleNodes(x2);

关键细节解释： #

• @taskMappings表变量：它的作用是把每个新生成的TaskID和对应的XML任务节点绑定在一起，这样我们就能明确每个TaskID对应的角色列表位置。
• OUTPUT子句：在INSERT Task时，inserted.TaskID是刚生成的自增ID，ParamValues.x1是当前插入的XML任务节点，把这两个值存入表变量，就建立了一一对应的关联关系。
• CROSS APPLY：用来遍历每个Task节点下的所有role子节点，结合表变量里的TaskID，就能批量生成所有需要插入的TaskRoles记录。

另外，我修正了你原代码里的一个小错误：ParamValues.x1.value('action[1]', 'INT)')中多写了一个右括号，避免执行时出现语法错误。

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