Snowflake INTO Statement

Question

I am trying to capture record count of table in a variable using INTO construct. Snowflake's documentation says:

https://docs.snowflake.com/sql-reference/constructs/into

but this does not work for me. Please see the query below:

SET myNo = 0;
SELECT COUNT(*)
INTO :myNo2
FROM myTable;

Error: INTO clause is not allowed in this context

Following syntax works:

SELECT $myNo;
SET myNo = (SELECT COUNT(*) FROM myTable);
SELECT $myNo;

I want to understand why is "INTO" construct not working? thanks.

Answer 1

the Into , will only work in the Snowflake Scripting block,

EXECUTE IMMEDIATE $$
DECLARE
  cnt INTEGER;
  BEGIN
  SELECT count(*) into :cnt  FROM DUAL;
  RETURN :cnt ;
END;
$$
;