'Invalid object name' error for Common Table Expression (CTE) even though CTE has been defined

Question

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 (
    ...
)

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

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?

Thanks

-Rohan.

Answer 1

The CTE's are defined only for one query. You would need to repeat them for the three selects or inserts:

with X_CTE . . .
INSERT INTO MyTable SELECT * FROM X_CTE;

with X_CTE . . .
INSERT INTO MyTable SELECT * FROM Y_CTE;

with X_CTE . . .
INSERT INTO MyTable SELECT * FROM Z_CTE;

Answer 2

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

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

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