creating a temp table from a "with table as" CTE expression

Question

I cannot seem to access a temp table based on the results of a CTE expression.

how do you create a temp table, and access the temp declared within a CTE.

in the example below, the last line will throw an error.

Thanks

 DECLARE @tbl TABLE
  ( 
   Id int
  ,ParentId int
  )
INSERT  INTO @tbl
        ( Id, ParentId )
select t_package.package_id, t_package.parent_ID from t_package 
;
WITH  abcd
        AS (
              -- anchor
            SELECT   id
                    ,ParentID
                    ,CAST(id AS VARCHAR(100)) AS [Path]
                    ,0 as depth
            FROM    @tbl
            WHERE   ParentId = 0
            UNION ALL
              --recursive member
            SELECT  t.id
                   ,t.ParentID
                   ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path]
                   ,a.depth +1
            FROM    @tbl AS t
                    JOIN abcd AS a ON t.ParentId = a.id
           )
SELECT * from abcd;

insert into #TMP (id,parent,branch,depth) (select * from abcd)

score 5 · Accepted Answer · answered Apr 25 '18 at 07:09

A set of CTEs introduced by a WITH clause is valid for the single statement that follows the last CTE definition. Here, it seems you should just skip the bare SELECT and make the INSERT the following statement:

WITH  abcd
        AS (
              -- anchor
            SELECT   id
                    ,ParentID
                    ,CAST(id AS VARCHAR(100)) AS [Path]
                    ,0 as depth
            FROM    @tbl
            WHERE   ParentId = 0
            UNION ALL
              --recursive member
            SELECT  t.id
                   ,t.ParentID
                   ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path]
                   ,a.depth +1
            FROM    @tbl AS t
                    JOIN abcd AS a ON t.ParentId = a.id
           )
insert into #TMP (id,parent,branch,depth) select * from abcd

select * from #TMP

(I've added the select from #TMP so that we still get a result set returned to the client, albeit that the insert and select statements are now reversed).

score 0 · Answer 2 · answered Apr 25 '18 at 07:10

You can use 2 statement of CTE one by one, CTE will run just after declaration. See the rule of CTE

So remove SELECT * from abcd; before this insert into #TMP (id,parent,branch,depth) (select * from abcd).

For Recursive CTE :When to use Common Table Expression (CTE)

score 0 · Answer 3 · answered Apr 25 '18 at 07:15

As a CTE is not persisted you can only use it once so once e.g.

WITH cte(id) AS 
(
SELECT 1
)

SELECT * FROM cte;

SELECT * FROM cte;

Would return the results '1' for the first call but then error with

Msg 208, Level 16, State 1, Line 8 Invalid object name 'cte'.

on the second.

You need to remove the

SELECT * from abcd;

before you populate your temp table or if you need to call it twice use a persisted method (temp table, table variable etc)

creating a temp table from a "with table as" CTE expression

3 Answers3