如何用插入的行数据填充CTE？CTE内临时表使用问题

首先，你遇到的问题是CTE的语法限制——CTE的定义块里不能包含变量声明（比如DECLARE @INSERTOUTPUT1 TABLE...），也不能在单个CTE成员里写多个语句的组合。咱们分两种正确的写法来解决这个问题：

方法1：先声明表变量，再用CTE查询插入结果 #

把表变量的声明放到CTE外面，先执行插入并把结果输出到表变量，再用CTE来引用这个表变量的数据：

-- 先在CTE外部声明表变量
DECLARE @INSERTOUTPUT1 TABLE ( BOOKID INT, BOOKTITLE NVARCHAR(50), MODIFIEDDATE DATETIME );

-- 执行插入并将结果输出到表变量
INSERT INTO BOOKS (BOOKID, BOOKTITLE, MODIFIEDDATE)
OUTPUT INSERTED.* INTO @INSERTOUTPUT1
VALUES(101, 'ONE HUNDRED YEARS OF SOLITUDE', GETDATE());

-- 用CTE引用表变量的数据
WITH RESULT AS (
    SELECT * FROM @INSERTOUTPUT1
)
SELECT * FROM RESULT;

方法2：直接将INSERT...OUTPUT作为CTE成员（无需表变量） #

如果你不需要保留插入结果用于后续其他操作，其实可以直接把带OUTPUT的INSERT语句作为CTE的成员，这样更简洁：

WITH RESULT AS (
    INSERT INTO BOOKS (BOOKID, BOOKTITLE, MODIFIEDDATE)
    OUTPUT INSERTED.*
    VALUES(101, 'ONE HUNDRED YEARS OF SOLITUDE', GETDATE())
)
SELECT * FROM RESULT;

为什么你的原写法不行？ #

• CTE的WITH子句里的每个CTE成员必须是单个SQL语句，不能包含变量声明（DECLARE）或者多个语句的组合。
• 表变量的作用域是当前批处理，所以必须在CTE定义之前声明，才能在CTE内部引用。

如果你的场景需要多次复用插入结果，方法1更合适；如果只是需要一次性获取插入的行数据，方法2更高效。

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