snowflake-cloud-data-platform

Create Temp table, CTE and insert into Temp table

In T-SQL, I am able to run and execute the following without issues

CREATE TEMP TABLE lmn
WITH CTEDefinition
 (
 select xyz from abc
 )
insert INTO lmn 
select from tables join CTE

But this seemingly simple construct is proving quite challenging to me. Can someone help with getting the right syntax for this?

Solution

  • In Snowflake you could perform it is a single step using CTAS:

    CREATE TEMPORARY TABLE lmn
AS
WITH CTEDefinition AS (
 select xyz from abc
)
select ... 
from tables 
join CTE
  ON ...;