Is it possible to wrap a string of CTE's together into one TEMP table in SQL?

Question

Within SQL Server is it at all possible to wrap a string of CTE's into just one temporary table in SQL?

Not all of the CTE's have equal amount of columns in nor are they the same data type so a UNION ALL is not possible, I thought the below may work however clearly I'm getting syntax errors:

SELECT * INTO #TEMP FROM (
    ;with firstCTE AS (
        SELECT   ColA   
                ,ColB
                ,Colc 
        FROM tbl1
    ) 
    ,secondCTE AS (
        SELECT   ColD   
                ,ColE
        FROM tbl2
    ) 
    ,thirdCTE AS (
        SELECT   ColF   
        FROM tbl3
    )
)

-- Selecting all of the data from the CTE's above
SELECT * FROM #TEMP

Answer 1

It depends on what you want to achieve:

1. If you need to UNION the result sets and they are not identical, you can CAST your columns to the same datatype, use NULLs for the columns that don't exist in one dataset and CAST the NULLs with the same datatype of the existing columns. Following your example:

    SELECT   ColA   
            ,ColB
            ,Colc
            ,CAST(NULL AS [use colD datatype]) AS ColD 
            ,CAST(NULL AS [use colE datatype]) AS ColE
            ,CAST(NULL AS [use colF datatype]) AS ColF 
    FROM tbl1

    UNION

    SELECT   
            CAST(NULL AS [use colA datatype]) AS ColA 
            ,CAST(NULL AS [use colB datatype]) AS ColB 
            ,CAST(NULL AS [use colC datatype]) AS ColC 
            ,ColD   
            ,ColE
            ,CAST(NULL AS [use colF datatype]) AS ColF 
    FROM tbl2

    UNION

    SELECT   
            CAST(NULL AS [use colA datatype]) AS ColA 
            ,CAST(NULL AS [use colB datatype]) AS ColB 
            ,CAST(NULL AS [use colC datatype]) AS ColC  
            ,CAST(NULL AS [use colD datatype]) AS ColD 
            ,CAST(NULL AS [use colE datatype]) AS ColE
            ,ColF   
    FROM tbl3

2. If you have a key through which you can JOIN these datasets together, you can use CTEs but it wouldn't be ideal to do so, especially if you have large datasets. You can instead push each dataset to a TEMP table, create indexes on the temp tables and join them together on a final temp table

CREATE TABLE #TMP1 (
            SomeIntegerKey INT NOT NULL INDEX IX0 NONCLUSTERED          
            ,ColA
            ,ColB
            ,Colc
)

INSERT INTO #TMP1 (
            SomeIntegerKey      
            ,ColA
            ,ColB
            ,Colc
)

SELECT  
            SomeIntegerKey
            ,ColA   
            ,ColB
            ,Colc 
FROM tbl1

CREATE TABLE #TMP2 (
            SomeIntegerKey INT NOT NULL INDEX IX0 NONCLUSTERED          
            ,ColD   
            ,ColE
)

INSERT INTO #TMP2 (
            SomeIntegerKey      
            ,ColD
            ,ColE
)

SELECT  
        SomeIntegerKey
        ,ColD   
        ,ColE
FROM tbl2

CREATE TABLE #TMP3 (
            SomeIntegerKey INT NOT NULL INDEX IX0 NONCLUSTERED          
            ,ColF
)

INSERT INTO #TMP3 (
            SomeIntegerKey      
            ,ColF
)

SELECT  
        SomeIntegerKey
        ,ColF
FROM tbl3

CREATE TABLE #TMPFINAL (
            SomeIntegerKey INT NOT NULL INDEX IX0 NONCLUSTERED          
            ,ColA   
            ,ColB
            ,Colc 
            ,ColD
            ,ColE
            ,ColF
)

INSERT INTO #TMPFINAL (
            SomeIntegerKey      
            ,ColA   
            ,ColB
            ,Colc 
            ,ColD
            ,ColE
            ,ColF
)

SELECT  
        tbl1.SomeIntegerKey
        ,tbl1.ColA   
        ,tbl1.ColB
        ,tbl1.Colc 
        ,tbl2.ColD
        ,tbl2.ColE
        ,tbl3.ColF
FROM tbl1
LEFT JOIN tbl2
    ON tbl1.SomeIntegerKey = tbl2.SomeIntegerKey
LEFT JOIN tbl3
    ON tbl1.SomeIntegerKey = tbl3.SomeIntegerKey

Keep in mind that a LEFT JOIN might not be what you need. Change to the proper JOIN condition that would work for you