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

Question

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

question from:https://stackoverflow.com/questions/65910681/converting-sql-server-to-snowflake-sql-how-do-i-write-the-insert-statement-to-p

Answer 1

Try the query without the values keyword.

INSERT INTO A1C_Min_Max (PAT_ID,TEST_CNT,ORD_ID_MIN,ORD_ID_MAX) 
SELECT

    oprc.PAT_ID,
    count(*)  "TEST_CNT",
    MIN(oprc.ORDER_PROC_ID)  "ORD_ID_MIN",
    MAX(oprc.ORDER_PROC_ID)  "ORD_ID_MAX"

FROM ORDER_PROC oprc
JOIN PAT_ENC enc
    ON oprc.PAT_ENC_CSN_ID = enc.PAT_ENC_CSN_ID
INNER JOIN ZC_DISP_ENC_TYPE typ
    ON enc.ENC_TYPE_C = typ.DISP_ENC_TYPE_C
INNER JOIN CLARITY_EAP eap
    ON oprc.PROC_ID = eap.PROC_ID
INNER JOIN 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;