Store parts as of a cte as temp-tables

Question

Consider the following cte

with Folders as 
(
    select a, b, c from foo
),
SubFolders as
(
    select 
          x.d
        , x.e 
    from
    (
        select 
              bla as d
            , boo as e
        from
            foobar
    ) x
    group by
        x.d,
        x.e
),
FullTable as
(
    select * from Folders
    cross join Subfolders
)

select * from Fulltable left join Subfolders on ...;

select s.d from Subfolders s; -- obviously does not work

(In reality, the cte has more expressions). I would now like query the resultset of Subfolders elsewhere in my script. The rest of the cte-expressions I only need once.

What is the most elegant way of doing this (plus: leaving the script as much as is)? I tried what is mentioned in Combining INSERT INTO and WITH/CTE but it gives an error: The SELECT list for the INSERT statement contains fewer items than the insert list even though I am positive that Subfolders ahs only 2 columns. What am I doing wrong?

.
.
.
),
FullTable as
(
    select * from Folders
    cross join Subfolders
)

insert into #temp (
      Subfolders.d
    , Subfolders.e
)

select * from Fulltable left join Subfolders on ...;

...

If I understand correctly, create separate temporary tables for each CTE. CTEs only get used once in a query, so you cannot both put them in a `select` query and stored them in a table. — Gordon Linoff, Aug 25 '14 at 11:49
Nopes cant be done, CTE will always be followed by an Immediate SELECT, UPDATE, DELETE statement, and thats it scope. wont be visible to any following statements also you cannot populate a temp or variable table inside a cte, im afraid you will have to rewrite that cte/part of cte again. — M.Ali, Aug 25 '14 at 11:49

score 1 · Accepted Answer · answered Aug 25 '14 at 11:53

A way around will be .....

SELECT * INTO #SubFolders 
FROM
(   select  x.d
          , x.e 
    from
    (  select bla as d
            , boo as e
        from foobar
    ) x
    group by x.d, x.e
)A



;with Folders as 
(
    select a, b, c from foo
),
FullTable as
(
    select * from Folders
    cross join #SubFolders
)
select * from Fulltable left join #SubFolders on ...;

select s.d from #SubFolders s; -- this will work

score 0 · Answer 2 · answered Aug 25 '14 at 11:49

0

Try this:

select Subfolders.d , Subfolders.e into #temp from Fulltable left join Subfolders on ...;

answered Aug 25 '14 at 11:49

Harsh

368
3
19

Store parts as of a cte as temp-tables

2 Answers2