Snowflake存储过程未插入记录，但没有错误

Question

谁能告诉我为什么这会运行，而不是实际插入记录？当我将实际查询作为变量返回时，代码在Snowflake中工作得很好：

CREATE OR REPLACE PROCEDURE Z_PC_FIVETRAN_DB.BLOB_RAW_CATALOGS.SP_FULL_AND_IS_HERRING_MOVE_CURRENT_DELETED_TO_PCFIVE(ACTION_TYPE VARCHAR,UPLOAD_ID VARCHAR,VENDOR VARCHAR)  
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$ 

var sql_textA = "INSERT INTO Z_PC_FIVETRAN_DB.BLOB_RAW_CATALOGS.RAW_CATALOGS (M_PDT_CODE) SELECT M_PDT_CODE FROM Z_CATALOGS_NEW.PUBLIC.CATALOGS_RAW WHERE M_VENDOR_NAME='HERRING' AND M_PDT_CODE NOT IN (SELECT M_PDT_CODE FROM Z_PC_FIVETRAN_DB.BLOB_RAW_CATALOGS.RAW_CATALOGS WHERE M_UPLOAD_ID='" +UPLOAD_ID+ "' AND M_PDT_CODE IS NOT NULL)"
var sql_commandA = snowflake.createStatement({ sqlText: sql_textA})

if (VENDOR=='HERRING' && ACTION_TYPE=='FULL')
{ 
var db = sql_commandA.execute()
return sql_textA
} 
else
{
return 'Not HERRING Or Not FULL'
}
$$; 

Answer 1

我运行了你的代码，并且能够插入行。我唯一能想到的就是如果AUTOCOMMIT为你关闭了，如果你需要显式调用COMMIT。尝试在INSERT之后调用COMMIT，看看它是否工作。

ALTER SESSION SET AUTOCOMMIT = FALSE;
CREATE OR REPLACE TABLE CATALOGS_RAW(M_VENDOR_NAME VARCHAR, M_PDT_CODE VARCHAR);
CREATE TABLE RAW_CATALOGS(M_PDT_CODE VARCHAR,M_UPLOAD_ID VARCHAR);

INSERT INTO CATALOGS_RAW VALUES('HERRING','PDT0002');
INSERT INTO CATALOGS_RAW VALUES('HERRING','PDT0003');
INSERT INTO CATALOGS_RAW VALUES('HERRING','PDT0005');
COMMIT;

SELECT * FROM CATALOGS_RAW;

INSERT INTO RAW_CATALOGS VALUES('PDT0001','UPD0001');
SELECT * FROM RAW_CATALOGS;



CREATE OR REPLACE PROCEDURE TESTPROC(ACTION_TYPE VARCHAR,UPLOAD_ID VARCHAR,VENDOR VARCHAR)  
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$ 
var sql_textA = "INSERT INTO RAW_CATALOGS (M_PDT_CODE) SELECT M_PDT_CODE FROM CATALOGS_RAW WHERE M_VENDOR_NAME='HERRING' AND M_PDT_CODE NOT IN (SELECT M_PDT_CODE FROM RAW_CATALOGS WHERE M_UPLOAD_ID='" +UPLOAD_ID+ "' AND M_PDT_CODE IS NOT NULL)"
var sql_commandA = snowflake.createStatement({ sqlText: sql_textA})

if (VENDOR=='HERRING' && ACTION_TYPE=='FULL')
{ 
var db = sql_commandA.execute()
return sql_textA
} 
else
{
return 'Not HERRING Or Not FULL'
}
$$; 

CALL TESTPROC('FULL','UPD0003','HERRING');
COMMIT;