How to join multiple CTE's into a temp table

Question

I have been given a script to clean up which uses approx 85 temp tables, I have been advised to use Common Table Expressions.

I have 3 CTE's, the first is the result of 7 tables pulled together using Union all. Followed by 2 more CTE's. The script runs up to:

select * from CTE_1
Union all 
select * from CTE_2
Union all
select * from CTE_3

I then want to put all these results into a reusable table so I can then add some joins with various case statement logic. How can I put these into a temp table so that I can reference it later.

I'm looking to reduce the amount of temp tables so rather than put each CTE into a temp table I would ideally put multiple CTE's into one temp table. I currently have:

; with [CTE One] as (
  select 1 as col
),
  [CTE Two]  as (
  select 2 as col
),
  [CTE Three]  as (
  select 3 as col
)
select * from CTE_1
Union all 
select * from CTE_2
Union all
select * from CTE_3

If the ctes can be unioned maybe you totally skip the cte and just union the queries together. I have to say to if you have a script with 85 temp tables, just migrating that to ctes isn't going to do much. Any logic that involved needs to be seriously rethought. — Sean Lange, Jan 17 '18 at 20:52
Possible duplicate of [SELECT INTO USING UNION QUERY](https://stackoverflow.com/questions/4018708/select-into-using-union-query) — Tab Alleman, Jan 17 '18 at 20:57

score 2 · Answer 1 · answered Jan 17 '18 at 21:09

2

Can't you just use into?

select *
into #temptable
from CTE_1
Union all 
select * from CTE_2
Union all
select * from CTE_3;

I might also be inclined to use a table variable, if the code is structured appropriately.

answered Jan 17 '18 at 21:09

Gordon Linoff

1,242,037
58
646
786

Von Abanes · Accepted Answer · 2018-01-17T22:27:36.260

Alternatively..

    IF ( OBJECT_ID('tempdb..#temptable') IS NOT NULL )
        BEGIN
            DROP TABLE #temptable
        END

    CREATE TABLE #temptable
    (
        val int
    )



   ;
    WITH    [CTE One]
              AS ( SELECT   1 AS col
                 ),
            [CTE Two]
              AS ( SELECT   2 AS col
                 ),
            [CTE Three]
              AS ( SELECT   3 AS col
                 )
        INSERT  INTO #temptable (val)
                SELECT  *
                FROM    ( SELECT    *
                          FROM      CTE_1
                          UNION ALL
                          SELECT    *
                          FROM      CTE_2
                          UNION ALL
                          SELECT    *
                          FROM      CTE_3
                        ) T

How to join multiple CTE's into a temp table

2 Answers2