Inserting values into newly created table from a pre-existing table using a cursor and for loop

I'm trying to insert values into a new table in the classic Snowflake SQL web interface using data from a table that was already created, a cursor, and a for loop. My goal is to insert new information and information from the original table into the new table, but when I try and run my code, there is an error where I am referring to the column of my original table. (See code below)


-- Creation and inserting values into table invoice_original 
create temporary table invoice_original (id integer, price number(12,2));
insert into invoice_original (id, price) values
  (1, 11.11),
  (2, 22.22);
  
  
--  Creates final empty table invoice_final
create temporary table invoice_final (
  study_number varchar,
  price varchar,
  price_type varchar);
  

execute immediate $$
declare
  c1 cursor for select price from invoice_original;
begin
  for record in c1 do
        insert into invoice_final(study_number, price, price_type)
        values('1', record.price, 'Dollars');
  end for;
end;
$$;

My end goal is to have the resulting table invoice_final with 3 columns - study_number, price, and price_type where the price value comes from the invoice_original table. The error I'm currently getting is:

Uncaught exception of type 'STATEMENT_ERROR' on line 6 at position 8 : SQL compilation error: error line 2 at position 20 invalid identifier 'RECORD.PRICE'.

Does anyone know why the record.price is not capturing the price value from the invoice_original table?

CodePudding user response：

there are a number of type of dynamic SQL that do not handle the cursor name, and thus give this error if you push it into a single name temp value it will work:

  for record in c1 do
        let temp_price number := record.price;
        insert into invoice_final(study_number, price, price_type)
        values('1', temp_price, 'Dollars');
  end for;

this sql has not been run, and could be the wrong format, but it is the base issue.

Also this really looks like an INSERT would work, but I also assume this is the nature of simplify the question down.

CodePudding user response：

I'm not able to explain it at the moment, but this works:

    -- Creation and inserting values into table invoice_original
    create
    or replace temporary table invoice_original (id integer, price number(12, 2));
insert into
    invoice_original (id, price)
values
    (1, 11.11),
    (2, 22.22);
--  Creates final empty table invoice_final
    create
    or replace temporary table invoice_final (
        study_number varchar,
        price number(12, 2),
        price_type varchar
    );
execute immediate $$
declare
  
  new_price number(12,2);
  c1 cursor for select price from invoice_original;
begin
  for record in c1 do
        new_price := record.price;
        insert into invoice_final(study_number, price, price_type) values('1',:new_price, 'Dollars');
  end for;
end;
$$;

Note that I changed the target table definition for price to NUMBER (12,2) instead of VARCHAR, and assigned the record.price to a local variable that was passed to the insert statement as :new_price.

That all said ... I would strongly recommend against this approach for loading tables for performance reasons. You can replace all of this with an INSERT .. AS ... SELECT.

Always opt for set based processing over cursor / loop / row based processing with Snowflake.

https://docs.snowflake.com/en/sql-reference/sql/insert.html