how to retrieve number of rows inserted through Snowflake javascript API?

Question

If I run an INSERT/SELECT in Snowflake through the javascript API, it looks like the rowCount returned is 1 regardless of how many rows were inserted...

myStatement = snowflake.createStatement( {sqlText: mySql} );
myStatement.execute();

rowCount = myStatement.getRowCount();

Is there a SIMPLE way to retrieve the number of rows inserted? Am I doing something wrong?

Thanks

Answer 1

No you are not doing something wrong. There is a difference in Snowflake from executing a query like an SELECT * .... from executing an UPDATE\DELETE\INSERT statement.

In snowflake if you perform a select, and what to know the number of returned rows then you are correct you execute:

myStatement = snowflake.createStatement( {sqlText: "SELECT * FROM TABLE1"} );
myStatement.execute();
rowCount = myStatement.getRowCount();

When you execute a 'INSERT\UPDATE\DELETE` snowflake returns just one row that as indicated on answer by @greg-pavlik has the number of modified rows. However there is even a more simple way.

myStatement = snowflake.createStatement( {sqlText: "INSERT ..."} );
myStatement.execute();
rowCount = myStatement.getNumRowsAffected();

Answer 2

When you insert rows outside of a stored procedure, you will get a single row in return that tells you the number of rows inserted. The same will happen in a stored procedure, so you need to grab the result set and read the number in the first row, first column of that result set.

create or replace temp table foo(v string);

-- This returns "number of rows", 2
insert into foo(v) values ('1'), ('2');

create or replace procedure foo()
returns string
language javascript
as
$$

    var mySql = "insert into foo(v) values ('1'), ('2');";
    var myStatement = snowflake.createStatement( {sqlText: mySql} );
    var rs = myStatement.execute();
    rs.next()
    return "Inserted " + rs.getColumnValue(1) + " rows.";
    
$$;

call foo();