CODEWITHSUNDEEP
azuresnowflake-cloud-data-platformetlprocedure
gaurav lalwani
gaurav lalwani

Reputation: 27

snowflake insert_querry procedure

I need to insert columns of one table into another table as a record

The structure will be like this:

table_name    Column_name      Distinct_Count

here is the code but I am not able to insert data:

create or replace procedure get_table_name(t string)
  returns string not null
  language javascript
  as     
  $$
  var column_var='select * from '+T+';'
  var statement1 = snowflake.createStatement({sqlText: column_var});
  var result_set1 = statement1.execute();
  var summation=''
    while (result_set1.next())  {
       summation=summation+result_set1.getColumnValue(1)+'\n';
       }
    var temp='create or replace temporary table mytemptable2 as select * from '+T+';'
    var statement2=snowflake.createStatement({sqlText:temp});
    var result_set2=statement2.execute();
    var total=0;
    while(result_set2.next())
    {
    total=total+result_set2.getColumnValue(1)+'\n';
    }
    var distinct_count='select Distinct count (*) from '+T+';'
    var statement3=snowflake.createStatement({sqlText:distinct_count});
    var result_set3=statement3.execute();
    while(result_set3.next())
    {
    row_count = result_set3.getColumnValue(1)+'\n';
    }
    var inserting= `INSERT INTO final_table(TABLENAME,COLUMNNAME) SELECT TABLE_NAME,COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='Gaurav' and TABLE_NAME = '$(T)';`

//    var inserting=`insert into final_table(TABLENAME,COLUMNNAME) values('tb1','id');`
    
    var statement4=snowflake.createStatement({sqlText:inserting});
    var result_set4=statement4.execute();
   var row_count1=0;
    while(result_set4.next())
    {
     row_count1=row_count1+result_set4.getColumnValue(1)+'\n';
    }
    return row_count1;
  $$
  ;

Upvotes: 0

Views: 41

Answers (1)

Pankaj
Pankaj

Reputation: 2746

Below is a version of code with insert - selecting from columns Though, not exactly sure where you are getting issue and if its an error in procedure or expected output not matching or not getting. Perhaps elaborate on that in the questions.

CREATE OR REPLACE procedure get_table_name(TVAL varchar)
returns string not null
LANGUAGE javascript
AS 
$$                                  
var querystr = "";
var sname = "PUBLIC";
querystr = "insert into final_table(tname,cname) select table_name,column_name from information_schema.columns where table_schema=? and table_name=?";
var statement = snowflake.createStatement({sqlText:querystr, binds:[sname,TVAL]});
var rs = statement.execute();
return "P";
$$
;

After executing above -

select * from FINAL_TABLE;
+-------+-------+
| TNAME | CNAME |
|-------+-------|
| D2    | NAME  |
| D2    | ID1   |
| D2    | ID2   |
+-------+-------+

select * from d2 where 1=2;
+-----+-----+------+
| ID1 | ID2 | NAME |
|-----+-----+------|
+-----+-----+------+

Also, wondering in case you are facing issue due to line-breaks, if so please refer - https://docs.snowflake.com/en/sql-reference/stored-procedures-javascript.html#line-continuation

Upvotes: 1

Related Questions