Snowflake How to get Records failed in Copy command

Question

is it possible to get records which failed during Copy command in Snowflake from internal stage to snowflake table?

I am trying to load error recrods in a error table during Copy command execution . Copy Command used:

Copy into table ( col1, col2,col3,col4) from ( select $1,$2,$3,56 from @%table) ON_ERROR=CONTINUE

Answer 1

To get all the bad records, you can run the copy with VALIDATION_MODE = 'RETURN ERRORS'. Then use the RESULT_SCAN from the validation in an insert statement.

Answer 2

If one of your columns is unique (i.e. col1), maybe you can compare rows in the table with the rows in the stage:

select $1 from @%table
MINUS 
select col1 from table;

Answer 3

Please check below select statement after copy command

select rejected_record  from table(validate(test_copy , job_id => '_last')) ;