SnowFlake Procedure Unload/Load data

Question

I am trying to creae a list of json object by processing the result set from a different query execution. The liast of json I want to persist to another table and copy the json to a stage so that later i can run the copy command and json data gets copied to the other table. How can I acheive this? Any thoughts.? The code is shared as screen shot in the image attached.

Code

Answer 1

In case you are only looking to persist/insert the resultset getting generated within your procedure then you can try to incorporate something like following in your code -

CREATE OR REPLACE procedure stproc1(anyvar varchar)
RETURNS varchar not null
LANGUAGE javascript strict
AS 
$$                                  
var rowarr = [];
var rowobj = {};
// BUILDING A DUMMY ARRAY
rowobj['processid'] = 100;
rowobj['loc'] = 200; 
rowobj['item'] = 300;
rowarr.push(rowobj);
// ENSURE TO nullify object BEFORE ANOTHER PUSH
rowobj={};
rowobj['processid'] = 10;
rowobj['loc'] = 20; 
rowobj['item'] = 30;
rowarr.push(rowobj);
len = rowarr.length;
var querystr = "";
var ret_str = "";
//Iterate though ARRAY to INSERT into target TABLE
for (var x = 0; x < len; x++) {
querystr = "insert into item_j values (?)";
var statement = snowflake.createStatement({sqlText:querystr, binds:[JSON.stringify(rowarr[x])] });
var rs = statement.execute();
}
return "BATCH";
$$
;

After executing above result will be stored in table ITEM_J that has only one column of type VARCHAR.

select * from item_j;
+-------+
| T_VAL |
|-------|
+-------+

call stproc1('a');
+---------+
| STPROC1 |
|---------|
| BATCH   |
+---------+

select * from item_j;
+----------------------------------------+
| T_VAL                                  |
|----------------------------------------|
| {"processid":100,"loc":200,"item":300} |
| {"processid":10,"loc":20,"item":30}    |
+----------------------------------------+
2 Row(s) produced.