sqlsnowflake-cloud-data-platformcommon-table-expressiontemp-tables

CREATE TEMPORARY TABLE IN SNOWFLAKE USING CTE

I am trying to create a temporary table in Snowflake using CTE. I will be using this table in the same session for another query.

Here is the syntax so far:

CREATE TEMPORARY TABLE NET_AVAIL AS (SELECT*
FROM CTE);

However, I am getting an error

Syntax error line 7 at position 0 Unexpected 'CREATE'

Can anyone please help with this?

I would be using this temporary table in the second part of the query so should I also mention ON COMMIT PRESERVE ROWS?

Solution

  • The correct order is:

    create temporary table a2 as

with cte as (select 1 x)

select *
from cte;