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
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
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;
$$;
I also got it working this way:
Pros : Dont have to worry about eventual consistency, using the last_query() function Cons : You need to define each column as a variable, if you have a wide table this is a pain!
DECLARE
v_rs RESULTSET;
BEGIN
v_rs := (EXECUTE IMMEDIATE v_copy_into_cmd);
for audit_item in v_rs do
v_file_name := audit_item."file";
v_status := audit_item."status";
v_rows_parsed := audit_item."rows_parsed";
v_rows_loaded := audit_item."rows_loaded";
insert into yustage.events_engine_audit
(
file_name ,
status ,
rows_parsed ,
rows_loaded
)
values
(
:v_file_name ,
:v_status ,
:v_rows_parsed ,
:v_rows_loaded
);
end for;
END;