CTE TO TEMP TABLE

  • Dear All,

    I am Facing a Problem in Putting a CTE Table data into a temp table.

    Following in the CTE Query which works Perfectly on its own (thanks to this forum members)

    But I want this data in a Temp Table.

    DECLARE@Files TABLE( LEVEL VARCHAR(50), PARENT Varchar(50), CHILD Varchar(50))

    INSERT@Files

    SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL

    SELECT'1' , 'A' , 'B' UNION ALL

    SELECT'1' , 'A' , 'C' UNION ALL

    SELECT'1' , 'A' , 'D' UNION ALL

    SELECT'1' , 'A' , 'E' UNION ALL

    SELECT'1' , 'A' , 'H' UNION ALL

    SELECT'2' , 'E' , 'G' UNION ALL

    SELECT'2' , 'E' , 'F' UNION ALL

    SELECT'2' , 'H' , 'M' UNION ALL

    SELECT'2' , 'H' , 'J' UNION ALL

    SELECT'2' , 'H' , 'I' UNION ALL

    SELECT'2' , 'J' , 'L' UNION ALL

    SELECT'3' , 'J' , 'K' ;

    WITH rCTE AS (

    SELECT

    Item_NO=CAST(ROw_NUMBER() OVER (ORDER BY Child ASC)-1 as varchar(max)),

    PART_NAME=Child

    FROM @Files

    WHERE LEVEL<=1

    UNION ALL

    SELECT

    Item_NO=P.Item_NO+'.'+CAST(ROw_NUMBER() OVER (ORDER BY Child ASC) as varchar(max)),

    PART_NAME=C.Child

    FROM rCTE P

    INNER JOIN @Files C

    ON P.PART_NAME=C.PARENT

    WHERE C.LEVEL>1)

    SELECT * FROM rCTE

    ORDER BY Item_NO ASC

    Thanks

    Ravi T

  • instead of this:

    SELECT * FROM rCTE

    ORDER BY Item_NO ASC

    insert into the temp table

    INSERT INTO #TempTable(Columns)

    SELECT columnlist

    FROM rCTE

    ORDER BY Item_No ASC;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply