0

Here is a very simple CTE:

;
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'.*

Community
  • 1
  • 1
emoreau99
  • 634
  • 6
  • 16
  • 3
    Because that's how CTEs are designed – Lamak Mar 18 '15 at 18:11
  • A CTE is essentially a temporary view, it exists in memory (rather than physically existing) for the duration of the single query and after that is removed. For more info see this post http://stackoverflow.com/questions/10196808/use-one-cte-many-times – legohead Mar 18 '15 at 18:15
  • 1
    From [the documentation](https://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx) - *A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. 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. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.* – GarethD Mar 18 '15 at 18:15

0 Answers0