CREATE TABLE FROM CTE's

• ExhibitA

  Ten Centuries

  Points: 1074

  More actions

  April 21, 2017 at 7:40 am

  #342534

  I have a series of complex CTE's, which I would like to use to drop the results into a table. 

  The table doesn't exist so it needs to be created, and I haven't created a new table from a CTE query before so I am a little unsure

  With CTE1 as 
  ( 
  Select  Column1
            ,column2 ... 
  from table 
  )
  ,
  CTE2 as(
  Select  Column1
            ,column2 .....
  from table2
  )

  Is it as straight forward as :

  USE [database]

  Create Table NEW_TABLE
  AS( Select column1,column2,.....coulumn_N
         from CTE2) ; 

  thanks in advance

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  April 21, 2017 at 7:46 am

  #1939314

  ExhibitA - Friday, April 21, 2017 7:40 AM

  I have a series of complex CTE's, which I would like to use to drop the results into a table. 

  The table doesn't exist so it needs to be created, and I haven't created a new table from a CTE query before so I am a little unsure

  With CTE1 as 
  ( 
  Select  Column1
            ,column2 ... 
  from table 
  )
  ,
  CTE2 as(
  Select  Column1
            ,column2 .....
  from table2
  )

  Is it as straight forward as :

  USE [database]

  Create Table NEW_TABLE
  AS( Select column1,column2,.....coulumn_N
         from CTE2) ; 

  thanks in advance

  You can use SELECT X,Y,Z INTO [table_name]
  😎

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  April 21, 2017 at 8:04 am

  #1939319

  You would need to do the inserts one at a time.
  WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
SELECT myTally.n
INTO tally1
FROM myTally


• ExhibitA

  Ten Centuries

  Points: 1074

  More actions

  April 21, 2017 at 8:44 am

  #1939328

  Great stuff! 

  thanks guys