Converting SQL Server to Snowflake SQL, how do I write the INSERT statement to put data from a query into a temp table?

I am working on converting a SQL Server query to Snowflake SQL. The code creates a dynamic start and end date, creates the temp table, then inserts the data from the query into the temp table.

Here is the query. When I run individual sections separately, I can create the variables and the temp table and the actual query to pull data also works.

However, I have not been able find the correct syntax for the INSERT statement to run correctly. Any help would be appreciated.

SET StartDate = (SELECT DATEADD(month, -24, dim.MONTH_BEGIN_DT)                   FROM HI_DB.STG_EPICCLARITY_PHS.DATE_DIMENSION dim                   WHERE CAST(GETDATE() AS date) = dim.CALENDAR_DT);    SET EndDate = (SELECT dim2.MONTH_BEGIN_DT                 FROM HI_DB.STG_EPICCLARITY_PHS.DATE_DIMENSION dim2                 WHERE CAST(GETDATE() AS date) = dim2.CALENDAR_DT);    CREATE TEMPORARY TABLE HI_DB.STG_EPICCLARITY_PHS.A1C_Min_Max  (      PAT_ID          VARCHAR(255),      TEST_CNT        NUMERIC(18,0),      ORD_ID_MIN      NUMERIC(18,0),      ORD_ID_MAX      NUMERIC(18,0)  );    INSERT INTO HI_DB.STG_EPICCLARITY_PHS.A1C_Min_Max (PAT_ID, TEST_CNT, ORD_ID_MIN, ORD_ID_MAX)    VALUES     SELECT      oprc.PAT_ID,      COUNT(*) as "TEST_CNT",      MIN(oprc.ORDER_PROC_ID) as "ORD_ID_MIN",      MAX(oprc.ORDER_PROC_ID) as "ORD_ID_MAX"  FROM       HI_DB.STG_EPICCLARITY_PHS.ORDER_PROC oprc  JOIN       HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc ON oprc.PAT_ENC_CSN_ID = enc.PAT_ENC_CSN_ID  INNER JOIN       HI_DB.STG_EPICCLARITY_PHS.ZC_DISP_ENC_TYPE typ ON enc.ENC_TYPE_C = typ.DISP_ENC_TYPE_C  INNER JOIN       HI_DB.STG_EPICCLARITY_PHS.CLARITY_EAP eap ON oprc.PROC_ID = eap.PROC_ID  INNER JOIN       HI_DB.STG_EPICCLARITY_PHS.ORDER_RESULTS ordres ON oprc.ORDER_PROC_ID = ordres.ORDER_PROC_ID  WHERE       oprc.ORDERING_DATE BETWEEN $StartDate AND $EndDate      AND enc.CONTACT_DATE BETWEEN $StartDate AND $EndDate      AND enc.SERV_AREA_ID = 12288      AND oprc.proc_id IN (12298843, 12299371, 122127749, 10050764, 12018926, 12037733)      AND ordres.COMPONENT_ID = 1202098                        -- USE COMPONENT_ID = 1005276 to get ESTIMATED AVERAGE GLUCOSE VALUE          AND LEN(ordres.ORD_VALUE) > 1  GROUP BY      oprc.PAT_ID    SELECT *  FROM HI_DB.STG_EPICCLARITY_PHS.A1C_Min_Max  

https://stackoverflow.com/questions/65910681/converting-sql-server-to-snowflake-sql-how-do-i-write-the-insert-statement-to-p January 27, 2021 at 07:18AM