How to Get Rows Affected by Last Snowflake SQL Query?

Databases such as Teradata (BTEQ), SQL Server, etc., allows you to get rows affected by the last DML statement using system variables. For example, ACTIVITY_COUNT in Teradata BTEQ. The row count of the last SQL query allows you to identify if there was any activity on table. Snowflake scripting provides system variables that hold the information of rows or records affected by an INSERT, UPDATE or DELETE queries in the current session. In this article, we will check how to get rows affected by last Snowflake SQL Query.

Rows Affected by Last SQL Query in Snowflake

Snowflake Scripting sets the global variable after each DML command is executed. These global variables are set for all DML statements, except SELECT statement. These variables does not have any effect on SELECT statements.

You can use these following variables to determine if the last SQL statement affected any rows.

SQLROWCOUNT: This variable holds the number of rows affected by the last DML statement.
SQLFOUND: This variable returns true if the last DML statement affected one or more rows.
SQLNOTFOUND: This variable returns true if the last DML statement affected zero rows.
getNumRowsAffected(): This is a JavaScript method and returns the number of rows affected by the last DML statement in JavaScript stored procedures. This is equivalent to SQLROWCOUNT in Snowflake Scripting and can be used to handle error in stored procedures.

Get Number of Rows Affected by Last Snowflake INSERT Query

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last INSERT statement.

execute immediate $$
begin
  -- Insert 3 rows into a table.
  insert into test_table values (1), (2), (3);
  
  -- Returns the number of rows affected by the INSERT statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 3 as an output.

Get Number of Rows Affected by Last Snowflake UPDATE Query

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last UPDATE statement.

execute immediate $$
begin
  UPDATE test_table
  SET col1 = 1
  WHERE col1 = 3;
  
  -- Returns the number of rows affected by the UPDATE statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 1 as an output.

Get Number of Rows Affected by Last Snowflake DELETE Query

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last DELETE statement.

execute immediate $$
begin
  DELETE FROM test_table
  WHERE col1 = 1;
  
  -- Returns the number of rows affected by the DELETE statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 2 as an output.

Get Number of Rows Affected by Last Snowflake MERGE Statement

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last MERGE statement with update.

execute immediate $$
begin
    merge into test_table
    using test_table2 as t2
    on (test_table.col1 = t2.col1)
    when matched then 
    UPDATE set col1 = t2.col1;
  
  -- Returns the number of rows 
  -- affected by the last Merge statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 2 as an output.

Get Number Rows Affected by Last Snowflake UPDATE Query in Snowflake JavaScript Procedure

The following Snowflake stored procedure written in JavaScript uses the getNumRowsAffected() JavaScript method to return the number of rows affected by the last UPDATE statement.

create or replace procedure stproc1()
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
        var rs = snowflake.execute (
            {sqlText: "UPDATE test_table SET col1 = 1 WHERE col1 = 1"}
            );
        return rs.getNumRowsAffected()
    $$
    ;

Above Snowflake JavaScript stored procedure returns 1 as an output.