Windows Server SQL多CTE配合多临时表使用时的识别问题

SQL Server 中 公共表表达式（CTE）的作用域仅限定于紧随其定义之后的第一个独立执行的SQL语句，该语句执行完成后CTE就会被系统自动销毁，因此你无法用同一组CTE定义同时支撑两个SELECT ... INTO插入操作。
另外你原代码本身还存在两处语法错误：

• 列名1a以数字开头，使用时需要用方括号包裹为[1a]
• CTE定义块内存在多余的右括号，结构不合法

这里提供两种可直接落地的实现方式：

方案1：拆分CTE定义，分别对应两次插入操作 #

这是改动最小的方式，两次插入前分别定义自己需要用到的CTE即可：

------TEMP TABLE SET UP------
IF EXISTS (
    SELECT * FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb..#LEFT')
)
BEGIN
    DROP TABLE #LEFT
END
IF EXISTS (
    SELECT * FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb..#RIGHT')
)
BEGIN
    DROP TABLE #RIGHT
END
------TEMP TABLE SET UP END------

-- 先插入#RIGHT，用到CTEfirst
;WITH CTEfirst ([1a], b, c, d) AS (
    SELECT [1a], b, c, d FROM tableA
)
SELECT * INTO #RIGHT FROM CTEfirst

-- 再插入#LEFT，用到相关CTE
;WITH 
CTEone (a, b, c) AS (
    SELECT a, b, c FROM table1
),
CTEtwo (a, b, c) AS (
    SELECT a, b, c FROM table2
),
CTEthree (a, b, c) AS (
    SELECT a, b, c FROM table3
)
SELECT * INTO #LEFT FROM CTEone 
UNION
SELECT * FROM CTEtwo 
UNION
SELECT * FROM CTEthree

方案2：公共CTE提前落地为临时表，后续复用 #

如果你的CTEfirst是后续逻辑还要多次用到的，可以提前将其数据存入临时表，避免重复定义和查询：

------TEMP TABLE SET UP------
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..#CTEfirst')) DROP TABLE #CTEfirst
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..#LEFT')) DROP TABLE #LEFT
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..#RIGHT')) DROP TABLE #RIGHT
------TEMP TABLE SET UP END------

-- 提前把公共CTE数据落地
;WITH CTEfirst ([1a], b, c, d) AS (
    SELECT [1a], b, c, d FROM tableA
)
SELECT * INTO #CTEfirst FROM CTEfirst

-- 插入#RIGHT直接查落地后的临时表
SELECT * INTO #RIGHT FROM #CTEfirst

-- 插入#LEFT
;WITH 
CTEone (a, b, c) AS (
    SELECT a, b, c FROM table1
),
CTEtwo (a, b, c) AS (
    SELECT a, b, c FROM table2
),
CTEthree (a, b, c) AS (
    SELECT a, b, c FROM table3
)
SELECT * INTO #LEFT FROM CTEone 
UNION
SELECT * FROM CTEtwo 
UNION
SELECT * FROM CTEthree

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