Attempting to save a resultset to a table. Snowflake SQL scripting

sure I am doing (or not doing) something simple. But if anybody can help that would be great!

I have a SQL scripting code block, where I iterate through a cursor and perform actions. The action in question is actually a copy into command. I save this to a result set and I can return it (which breaks the cursor) to screen and looks fine. So I can see the output of the copy into command. I want to save that to a audit table, So I thought Easy I have the result set, just need to insert that into a table. But unfortunately, i am not getting something correct as I can't get that bit to work. Can anybody help?

Code block example is included.

The population of v_copy_into_cmd is passed from the cursor, the copy into command works fine.

DECLARE
v_rs RESULTSET;


 begin
 v_rs := (EXECUTE IMMEDIATE v_copy_into_cmd);
 insert into yustage.events_engine_audit 
 (
 file_name ,
 status ,
 rows_parsed ,
 rows_loaded ,
 error_limit ,
 errors_seen ,
 first_error ,
 first_error_line ,
 first_error_character ,
 first_error_column_name 
 )
 select 
 file_name ,
 status ,
 rows_parsed ,
 rows_loaded ,
 error_limit ,
 errors_seen ,
 first_error ,
 first_error_line ,
 first_error_character ,
 first_error_column_name 
 from table(v_rs);
 return table(v_rs);

Helpful error message is this : syntax error line 59 at position 14 unexpected '.'. syntax error line 59 at position 23 unexpected '='. (line 66)

If I comment the insert statement out, the code runs fine.

I expected the result of the copy into command to be inserted into my audit table

Solution

you can use TABLE(RESULT_SCAN(LAST_QUERY_ID())) to get the result set for your copy operation, see this example in snowflake community.

EDIT : column name with quotes

I have tested something like below :

CREATE OR REPLACE PROCEDURE insert_result_set_data()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- you can execute other commands(or your COPY command) here
    EXECUTE IMMEDIATE 'SELECT * FROM sample_result_set';

    INSERT INTO events_engine_audit (
        file_name, status, rows_parsed, rows_loaded
    )
    SELECT 
        "file" as file_name, "status" as status, "rows_parsed" as rows_parsed , "rows_loaded" as rows_loaded
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    RETURN 'Insert operation completed successfully';
END;
$$;