Snowflake SQL Procedure and Cursor

Question

I am working on a SQL procedure to read data from cursor and insert into table. How do i reference the cursor values in insert statement.

Below is what i have tried:

CREATE OR REPLACE PROCEDURE SP_FACT_SALES()
returns varchar(100)
LANGUAGE SQL
EXECUTE AS OWNER
AS '
declare
    
    INVOICE_NUMBER varchar(15);
    CUSTOMER    VARCHAR(20);
    AMOUNT  NUMBER(17,2);
    c1 cursor for select INVOICE_NUMBER,CUSTOMER,AMOUNT from db.schema.v_fact_sales limit 3;
begin
    open c1;
    
    fetch c1 into INVOICE_NUMBER,CUSTOMER,AMOUNT ;
    
    
    insert into db.schema.fact_sales(INVOICE_NUMBER,CUSTOMER,AMOUNT)
    values(c1.INVOICE_NUMBER,c1.CUSTOMER,c1.AMOUNT)
end ;
';

I am getting below error:

Uncaught exception of type 'STATEMENT_ERROR' on line 14 at position 1 : SQL compilation error: error line 2 at position 11 invalid identifier 'C1.INVOICE_NUMBER'

Do i have to FOR loop to reference cursor values or is there so way to bulk collect(similar to Oracle)

Any help would be appreciated!

Answer 1

You need to use a FOR loop to iterate through the cursor: https://docs.snowflake.com/en/sql-reference/snowflake-scripting/for.html