CTE TO TEMP TABLE

• santa326

  SSC Eights!

  Points: 925

  More actions

  July 3, 2015 at 11:15 am

  #299725

  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

• pietlinden

  SSC Guru

  Points: 63523

  More actions

  July 3, 2015 at 12:26 pm

  #1810242

  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;