即使已定义 CTE，也会出现公共表表达式 (CTE) 的“无效对象名称"错误 [英] 'Invalid object name' error for Common Table Expression (CTE) even though CTE has been defined

问题描述

我使用的是 SQL Server 2012.我在一行中定义了三个 CTE，如下所示:

I am using SQL server 2012. I have three CTEs defined in a row as shown below:

;WITH X_CTE (A, B, C, D)
AS (
    ...
)
,
Y_CTE (A, B, C, D)
AS (
    ...
)
,
Z_CTE (A, B, C, D)
AS (
    ...
)

然后，我将这些 CTE 插入到一个表中，该表的架构已定义并与 CTE 的架构相匹配

Then, I insert these CTEs into a table, whose schema is defined and matches that of the CTEs

INSERT INTO MyTable SELECT * FROM X_CTE
INSERT INTO MyTable SELECT * FROM Y_CTE
INSERT INTO MyTable SELECT * FROM Z_CTE

我收到三个 INSERT INTO 语句中 CTE 的无效对象名称"错误.事实上，我在 SELECT 语句中遇到了同样的错误:

I am getting a 'Invalid object name' error for the CTEs in the three INSERT INTO statements. In fact, I get the same errors with SELECT statements:

SELECT * FROM X_CTE
SELECT * FROM Y_CTE
SELECT * FROM Z_CTE

请您指出这里有什么问题吗?

Would you please point out what is wrong here?

谢谢

-罗汉.

推荐答案

CTE 只为紧随其后的 一个 语句定义.三个 INSERT 语句 - 好吧 - 不止一个语句.

CTEs are only defined for one statement that follows them. Three INSERT statements are - well - more than one statement.

由于所有插入都在同一个表中，您可以执行 UNION ALL 将所有行收集到单个 INSERT 语句中:

Since all of the inserts are to the same table, you can do a UNION ALL to gather all of the rows into a single INSERT statement:

INSERT INTO MyTable
SELECT * FROM X_CTE
UNION ALL
SELECT * FROM Y_CTE
UNION ALL
SELECT * FROM Z_CTE

但我也会更改上述内容以使用显式列列表 - 如果稍后将更多列添加到 MyTable，您不希望 此 查询中断:

But I'd also change the above to use explicit column lists - you don't want this query breaking if more columns are added to MyTable later:

INSERT INTO MyTable (A,B,C,D)
SELECT * FROM X_CTE
UNION ALL
SELECT * FROM Y_CTE
UNION ALL
SELECT * FROM Z_CTE

这篇关于即使已定义 CTE，也会出现公共表表达式 (CTE) 的“无效对象名称"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！