Inserting Values into a Table Using Stored Procedure in Snowflake

When I execute your stored procedure (assume TABLE_NAME = FOO), it returns an error:

Execution error in store procedure GET_COLUMNS: SQL compilation error: error line 1 at position 26 invalid identifier 'FOO' At Statement.execute, line 18 position 15

If you look at the SQL History, you will see that you are generating the following INSERT statement:

INSERT INTO TABLE1 VALUES(FOO,COL1);

which is missing single quotes around the literal values; it should be:

INSERT INTO TABLE1 VALUES('FOO','COL1');

To correct this, you could change this:

         var stmt2 = snowflake.createStatement({
         sqlText: "INSERT INTO TABLE1 VALUES(" + TABLE_NAME + "," + cols[i] + ")" + ";"
              });

to this:

         var stmt2 = snowflake.createStatement({
         sqlText: "INSERT INTO TABLE1 VALUES('" + TABLE_NAME + "','" + cols[i] + "')" + ";"
              });

That said, I would approach this from a different angle entirely. A more efficient way to retrieve metadata information (such as column names) is via INFORMATION_SCHEMA views. If you do NOT need to return the array of column names as output for the stored procedure, you could use something like this:

CREATE OR REPLACE PROCEDURE get_columns(TABLE_NAME VARCHAR)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
  let sql_insert = `
INSERT INTO TABLE1 (
  tab_name
 ,col_name
)
SELECT TABLE_NAME
      ,COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = ?
`;

  snowflake.execute({"sqlText" : sql_insert, "binds" : [TABLE_NAME]});
  
  return [];
$$
;

If you DO require that the stored procedure return the column names in an output array, then you could use something like this:

CREATE OR REPLACE PROCEDURE get_columns(TABLE_NAME VARCHAR)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
  let sql_get_cols = `
SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = ?
 ORDER BY ORDINAL_POSITION
`;

  let sql_insert = `
INSERT INTO TABLE1 (
  tab_name
 ,col_name
)
VALUES (?, ?)
`;

  let rs_get_cols = snowflake.execute({"sqlText" : sql_get_cols, "binds" : [TABLE_NAME]});
  
  let cols = [];
  while (rs_get_cols.next()) {
    let column_name = rs_get_cols.getColumnValue(1);
    snowflake.execute({"sqlText" : sql_insert, "binds" : [TABLE_NAME, column_name]});
    cols.push(column_name);
  }
  
  return cols;
  
$$
;

And there are other variations on this as well.