asked on 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 test
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'.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Éric Moreau
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
CTE was created as a destroyable view which persists for a single statement, and then automatically disappears.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>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.