Getting same value for all records in snowflake

November 30, 2021

I have key_value table and key table. I have to load only key from key_value table and insert into key table using snowflake procedure. I have written below code. After executing it i am getting same key value in key table rather all the keys.

create or replace procedure proc_key_load()
returns varchar
language javascript
as
$$
 var query=`select key from key_value`;
 var ret=snowflake.createStatement( {sqlText: query}).execute();
 var length=ret.getRowCount();
 var counter=0;
 while(counter<length){
 ret.next();
 var value=ret.getColumnValue(1);
 var load_query=`insert into key_load values(` + value +`)`;
 var ret=snowflake.createStatement( {sqlText: load_query}).execute();
 counter += 1;
 }
 return 'SUCCESS';
$$

>Solution :

I didn’t test it but as I see that you redefine "ret" in the loop. Try with assigning different variable:

create or replace procedure proc_key_load()
returns varchar
language javascript
as
$$
 var query=`select key from key_value`;
 var ret=snowflake.createStatement( {sqlText: query}).execute();
 var length=ret.getRowCount();
 var counter=0;
 while(counter<length){
 ret.next();
 var value=ret.getColumnValue(1);
 var load_query=`insert into key_load values(` + value +`)`;
 var ret2 = snowflake.createStatement( {sqlText: load_query}).execute();
 counter += 1;
 }
 return 'SUCCESS';
$$

Update: I tested the above code and it works. If you just need to copy some data from one table to another, you can just plain SQL right? Something like:

INSERT INTO key_load SELECT key FROM key_value;

Anyway, if you really need to use the above procedure, you can write it in a more efficient way:

create or replace procedure proc_key_load()
returns varchar
language javascript
as
$$
 var query=`select key from key_value`;
 var ret=snowflake.createStatement( {sqlText: query}).execute();
 while(ret.next()){
   var value=ret.getColumnValue(1);
   var load_query=`insert into key_load values(?)`;
   snowflake.createStatement( {sqlText: load_query, binds:[value]  }).execute();
 }
 return 'SUCCESS';
$$;