CTE - cannot query the results twice
Here is a very simple CTE:
Why can't I select twice on the CTE table? The second select statement returns an error:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 9
Invalid object name 'test'.
;
WITH test
AS
(
SELECT GETDATE() AS CurrentDate
)
SELECT * FROM test
SELECT * FROM testWhy can't I select twice on the CTE table? The second select statement returns an error:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 9
Invalid object name 'test'.
ASKER
I know it is not available after the first query. I am looking for a reference of the official reason on why it is like this.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
CTE was created as a destroyable view which persists for a single statement, and then automatically disappears.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>I am looking for a reference of the official reason on why it is like this.
First paragraph...
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
First paragraph...
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
As to "why", I think because that's what a CTE was designed and intended to be. You can already use temp or work tables if you want to query a result multiple times. In particular, CTEs were used to implement recursion, which added a great new feature to SQL Server.
ASKER
Thanks
After that, it's gone.
If you need it to live throughout a SP/whatever, then create a temporary table (#temp), insert into that temp table, and use it.