Snowflake using CTE to create table

user_11058930 0
Viewed 2399

Does Snowflake allow you to create a series of CTEs then join them together at the end to create a table?

For example:

with CTE1 as ( SELECT * FROM TABLE1)
,CTE2 AS (SELECT * FROM TABLE2)
,CTE3 AS (SELECT * FROM TABLE3)

CREATE TABLE TABLE_NAME_HERE AS 
SELECT * FROM CTE1 AS 1
LEFT JOIN CTE2 AS 2 ON 1.KEY = 2.KEY
LEFT JOIN CTE3 AS 3 ON 1.KEY = 3.KEY

I'm getting a unexpected 'CREATE'. error

snowflake-cloud-data-platform
2 Answers

It's an interesting one, because that CTAS (CREATE TABLE AS) form is

CREATE TABLE <name> AS <select>

and SELECT form is can have a CTE and a CTE form is

WITH <name> AS <select>

CTE can also be in sub-select given "it's just a select", thus

SELECT <columns> 
FROM (
    WITH cte_1 AS (
         SELECT <columns>
         FROM table
    )
    SELECT <columns>
    FROM cte_1
)

which shows why the form that Lukasz shows is correct because if we add more parens

CREATE TABLE name AS (
    WITH cte_1 AS (
        SELECT <columns>
        FROM table
    )
    SELECT <columns>
    FROM cte_1
)
user_439920

Yes, it is possible:

CREATE TABLE TABLE_NAME_HERE AS 
WITH CTE1 as ( SELECT * FROM TABLE1)
    ,CTE2 AS (SELECT * FROM TABLE2)
    ,CTE3 AS (SELECT * FROM TABLE3)
SELECT *     -- here should be explicit column list to avoid name duplication error
FROM CTE1 AS 1
LEFT JOIN CTE2 AS 2 ON 1.KEY = 2.KEY
LEFT JOIN CTE3 AS 3 ON 1.KEY = 3.KEY;
user_50708790
Related