CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
GigaRohan
GigaRohan

Reputation: 747

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

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.

Upvotes: 12

Views: 17079

Answers (4)

Leon van Wyk
Leon van Wyk

Reputation: 689

Had the same issue on the first execution of a SQL function that contained a CTE. I was able to replicate the error every time after running:

DBCC FREESYSTEMCACHE(N'ALL');

The solution is simple, just recompile the function:

exec sp_refreshsqlmodule '<function-name>';

Upvotes: 0

Raphael Ribeiro
Raphael Ribeiro

Reputation: 539

Maybe you are only selecting and running the select statement, you need to run all, the select and cte statements.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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;

Upvotes: 12

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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

Upvotes: 17

Related Questions