azure - 雪花 insert_query 过程

Question

我需要将一个表的列作为一条记录插入到另一个表中

结构如下:

table_name    Column_name      Distinct_Count

这是代码，但我无法插入数据:

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;
  $$
  ; 

Answer 1

下面是带有插入的代码版本 - 从列中选择不过，不确定您在哪里遇到问题，以及是否是过程中的错误或预期输出不匹配或未得到。也许在问题中详细说明这一点。

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";
$$
;

执行上述操作后 -

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

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

此外，想知道您是否因换行而面临问题，如果是这样，请参阅 - https://docs.snowflake.com/en/sql-reference/stored-procedures-javascript.html#line-continuation