ignore the exception and load data into snowflake-CodePudding

In snowflake, I am able to get the error details using TRY-CATCH method. I mocked up a data where I am trying to insert a VARCHAR data into INTEGER data type column. This exception was caught in try-catch method used in my snowflake procedure. As usual, the procedure failed and none of the records were inserted. I want to ignore this BAD data and insert rest of the records into my target table using snowflake procedure. IS THIS POSSIBLE? Also I need to INSERT that BAD data into another table. How can we achieve this? Please share you thoughts/expertise. Thanks, Joe.

CodePudding user response：

if you are using Snowflake procedure/ function you can write a common Error routine in captures the errors in a table and call it in your procedure. Please check this link for a common error handler which i wrote few months back.

https://github.com/hkandpal/Snowflake/blob/main/Snow_Erro_Handling.txt

CodePudding user response：

I am trying to insert a VARCHAR data into INTEGER data type column.

I want to ignore this BAD data and insert rest of the records into my target table using snowflake procedure

If the source is VARCHAR then assumption that it contain INTEGER may be dangerous. Instead of trying to insert incorrect data and fail entire query alternative approach could validate data. TRY_CAST:

A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e. converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.

-- pass only NULLs and validated rows
INSERT INTO tab(col_int)
SELECT col_varchar
FROM source_table
WHERE TRY_CAST(col_varchar AS INT) IS NOT NULL
   OR col_varchar IS NULL;

-- error table, log 
INSERT INTO error_table(col_varchar)
SELECT col_varchar
FROM source_table
WHERE TRY_CAST(col_varchar AS INT) IS NULL
  AND col_varchar IS NOT NULL;