I have to pass the dynamic value as my column name to my table from the loop and the constant value . My code snippet something looks like below:
While(res.next()){
var columnname= res.getColumnValue(1);
var stmt= insert into table1(column1,column2,column3)
select column1,'Too Long',+columnname+from table2;
\var result= \snowflake.createStatement(stmt);
\executing the statement
Getting error as invalid identifier if I pass the constant value. How can we achieve this?
It seems there's a syntax error on your procedure. Be careful about how you escaped single quote character. It's hard to debug it without seeing the actual source but here's a sample script to demonstrate there is nothing special with the constant value:
CREATE or REPLACE PROCEDURE TEST( VAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS '
var columnname = VAR1
for (var i = 0; i< 5; i++){
var stmt = snowflake.createStatement( { sqlText: "insert into table1(column1,column2,column3) select column1,''Too Long''," + columnname + " from table2" } );
res = stmt.execute();
}
return ''YES''
';
To test the above procedure, I create two tables and insert a row to table2:
create table table1(column1 varchar,column2 varchar,column3 varchar);
create table table2(column1 varchar,columnX varchar);
insert into table2 values ('testing','secretcol');
I call the procedure with "columnX" variable, I did not want write more complex procedure to read the column name from 3rd table:
call test( 'columnX' );
When I query the table1, I see 5 records. Each row contains: 'testing','Too long' and 'secretcol' values.
select * from table1;
