'Invalid object name' error for Common Table Expression (CTE) even though CTE has been defined
common-table-expression, sql, sql-server
Solution
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
Problem
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.