能否在单条CTE流水线中执行多表INSERT操作？

输入表 #

input(code, persons)
'ID1', 'Pete Sampras,Ricky Martin,Albert Einstein'
'ID2', 'Georges Mikael, Diego Maradona'

需要生成的三张输出表 #

ids_table(idcode, code)
1, 'ID1'
2, 'ID2'

persons(idperson, FirstName, LastName)
1, 'Pete', 'Sampras'
2, 'Ricky', 'Martin'
3, 'Albert', 'Einstein'
4, 'Georges', 'Mikael'
5, 'Diego', 'Maradona'

code_persons(idcode, idperson)
1, 1
1, 2
1, 3
2, 4
2, 5

我尝试用CTE流水线实现这个需求，想知道能不能在同一CTE流程里完成多表INSERT操作，写了如下示例代码：

WITH cte AS (
    SELECT code, [value] AS person
    FROM input
    CROSS APPLY STRING_SPLIT(persons, ',')
), cte2 AS (
    SELECT code, person, CHARINDEX(' ',person) AS splitIndex
    FROM cte
), cte3 AS (
    SELECT code, person, LEFT(person, splitIndex) AS FirstName,
        SUBSTRING(person, splitIndex, 100) AS LastName
    FROM cte2
), cte4 AS (
    INSERT INTO ids_table(code)
    SELECT DISTINCT code 
    FROM cte3
), cte5 AS (
    INSERT INTO persons(FirstName, LastName)
    SELECT FirstName, LastName
    FROM cte3
), cte6 AS (
    INSERT INTO code_persons(idcode, idpersons)
    SELECT it.idcode, p.idperson
    FROM cte3
    JOIN persons p ON p.FirstName = cte3.FirstName AND p.LastName=cte3.LastName
    JOIN ids_table it ON cte3.code=it.code
)
--Some code to trigger execution

目前我想到的方案是把cte3的结果存入临时表，请问有没有办法避免使用临时表？

不能直接在CTE流水线里串联多个INSERT操作，因为CTE定义中不允许包含这类数据修改语句（除非是带OUTPUT的INSERT且需返回结果）。要避免物理临时表，可以利用OUTPUT子句捕获插入的自增ID，结合表变量来完成关联插入，具体实现如下：

-- 预处理原始数据：拆分人员列表并分割姓名
WITH preprocessed AS (
    SELECT 
        code,
        -- 清理STRING_SPLIT后可能带的前导空格
        LTRIM([value]) AS full_name,
        CHARINDEX(' ', LTRIM([value])) AS split_pos
    FROM input
    CROSS APPLY STRING_SPLIT(persons, ',')
),
name_split AS (
    SELECT
        code,
        LEFT(full_name, split_pos - 1) AS FirstName,
        SUBSTRING(full_name, split_pos + 1, LEN(full_name)) AS LastName
    FROM preprocessed
)
-- 声明表变量存储插入后的ID映射（替代物理临时表）
DECLARE @ids_map TABLE (idcode INT, code VARCHAR(10));
DECLARE @persons_map TABLE (idperson INT, FirstName VARCHAR(50), LastName VARCHAR(50));

-- 插入ids_table并捕获生成的idcode
INSERT INTO ids_table(code)
OUTPUT inserted.idcode, inserted.code INTO @ids_map
SELECT DISTINCT code FROM name_split;

-- 插入persons并捕获生成的idperson
INSERT INTO persons(FirstName, LastName)
OUTPUT inserted.idperson, inserted.FirstName, inserted.LastName INTO @persons_map
SELECT FirstName, LastName FROM name_split;

-- 插入关联表code_persons
INSERT INTO code_persons(idcode, idperson)
SELECT 
    im.idcode,
    pm.idperson
FROM name_split ns
JOIN @ids_map im ON ns.code = im.code
JOIN @persons_map pm ON ns.FirstName = pm.FirstName AND ns.LastName = pm.LastName;

关键说明 #

1. 用CTE完成一次性数据预处理，避免重复拆分和计算逻辑；
2. 使用表变量替代物理临时表，表变量在会话结束后自动销毁，无需手动清理，数据量较小时性能更优；
3. 通过OUTPUT子句直接捕获插入后的自增ID，避免后续查询原表关联时出现重复数据匹配错误；
4. 最后通过预处理的CTE结果关联两个表变量，完成关联表的插入。

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