将CTE查询结果插入其他表提示无效对象名cte如何解决？

• 直接原因是CTE的作用域限制：SQL Server 中 CTE 的有效范围仅为其定义后紧跟的第一条SQL语句。你的代码中CTE定义后先执行了SELECT DISTINCT TOP 5 ...查询，这条语句执行完成后CTE就会被销毁，后续INSERT语句再引用cte时自然会报「无效对象名」的错误。
• 隐性问题：你INSERT语句中直接写SELECT * FROM cte，但原始CTE返回的字段是reorderhistoryId、dateCreated、personId、reorderId、statusNo、createdBy、OrganizationId共7个字段，和插入目标表rouletteTracking要求的9个字段数量、顺序都不匹配，即使解决作用域问题也会触发字段不匹配的报错。

根据你的使用场景二选一即可：

场景1：仅需要把查询结果插入目标表 #

直接把INSERT语句放到CTE后第一条执行，将你写的查询逻辑作为插入的数据源，示例代码如下：

;WITH cte AS
(
    SELECT 
        rd.reorderhistoryId,
        rd.dateCreated,
        rd.personId,
        rd.reorderId,
        rd.statusNo,
        rd.createdBy,
        r.OrganizationId
    FROM 
        (SELECT 
             MIN(reorderhistoryId) AS reorderhistoryId,
             MIN(personId) AS personId,
             reorderId
         FROM 
             reorderHistoryDetails
         GROUP BY 
             reorderId) rh 
    INNER JOIN 
        reorderHistoryDetails rd ON rd.reorderhistoryId = rh.reorderhistoryId
    INNER JOIN 
        reorderDetails r ON r.personId = rd.personId
    WHERE 
        rd.statusNo = 1059
)
INSERT INTO rouletteTracking (personId, reorderStatusType, productId,
                              amountPaid, reorderId, orgId, 
                              createdBy, dateCreated, timeStamp)
SELECT DISTINCT TOP 5
    rlst.personId,
    'Start Reorder Process',
    1,
    3,
    rlst.reorderId,
    rlst.OrganizationId,
    rlst.createdBy,
    rlst.dateCreated,
    rlst.dateCreated
FROM
    cte rlst
INNER JOIN 
    doctororderall d ON rlst.personId = d.personId
                     AND d.productId = 1
                     AND YEAR(rlst.dateCreated) = YEAR(d.dateCreated)
INNER JOIN 
    patientdetails pd ON pd.personId = d.personId
WHERE 
    rlst.datecreated > '2020-09-01'
    AND rlst.dateCreated <= '2021-10-06'
-- 如果需要同步返回插入的内容，可新增下面一行
-- OUTPUT inserted.*

场景2：需要多次复用CTE的结果执行多个操作 #

将CTE的结果提前存入临时表，后续所有操作都可以引用临时表，不受作用域限制，示例代码如下：

-- 将CTE结果存入临时表，可多次复用
SELECT *
INTO #tempCteData
FROM (
    SELECT 
        rd.reorderhistoryId,
        rd.dateCreated,
        rd.personId,
        rd.reorderId,
        rd.statusNo,
        rd.createdBy,
        r.OrganizationId
    FROM 
        (SELECT 
             MIN(reorderhistoryId) AS reorderhistoryId,
             MIN(personId) AS personId,
             reorderId
         FROM 
             reorderHistoryDetails
         GROUP BY 
             reorderId) rh 
    INNER JOIN 
        reorderHistoryDetails rd ON rd.reorderhistoryId = rh.reorderhistoryId
    INNER JOIN 
        reorderDetails r ON r.personId = rd.personId
    WHERE 
        rd.statusNo = 1059
) t

-- 执行第一条查询操作
SELECT DISTINCT TOP 5
    rlst.personId AS personId,
    'Start Reorder Process' AS reorderStatusType,
    1 AS productId,
    3 AS amountPaid,
    rlst.reorderId,
    rlst.OrganizationId AS orgId,
    rlst.createdBy,
    rlst.dateCreated,
    rlst.dateCreated AS timeStamp
FROM
    #tempCteData rlst
INNER JOIN 
    doctororderall d ON rlst.personId = d.personId
                     AND d.productId = 1
                     AND YEAR(rlst.dateCreated) = YEAR(d.dateCreated)
INNER JOIN 
    patientdetails pd ON pd.personId = d.personId
WHERE 
    rlst.datecreated > '2020-09-01'
    AND rlst.dateCreated <= '2021-10-06'

-- 执行第二条插入操作，注意手动匹配插入字段
INSERT INTO rouletteTracking (personId, reorderStatusType, productId,
                              amountPaid, reorderId, orgId, 
                              createdBy, dateCreated, timeStamp)
SELECT 
    personId,
    'Start Reorder Process',
    1,
    3,
    reorderId,
    OrganizationId,
    createdBy,
    dateCreated,
    dateCreated
FROM #tempCteData
-- 可自定义过滤条件
WHERE datecreated > '2020-09-01' AND dateCreated <= '2021-10-06'

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