In Snowflake, I have an empty table (MyTable) that I will load via a copy from a csv. When done, I need to find the max value of an INT Id (MyId) column and reseed a sequence (MyIdSequence) to that value.

I am doing this inside the Snowsight UI.

How do I reseed the sequence using this dynamically queried value inside of the Snowsight UI? This (see below) does not work because I cannot query a value inside the CREATE SEQUENCE statement:

CREATE OR REPLACE SEQUENCE MyIdSequence START = (SELECT MAX(MyId) FROM MyTable)

>Solution :

Using Snowflake Scripting block:

BEGIN
  LET sql TEXT := CONCAT('CREATE OR REPLACE SEQUENCE MyIdSequence START = ', 
                         (SELECT MAX(MyId)+1 FROM MyTable));
  EXECUTE IMMEDIATE sql;
  RETURN sql;
END;

For input table:

CREATE OR REPLACE TABLE MyTable(MyId INT) AS SELECT 1 UNION SELECT 2;

Output:

CREATE OR REPLACE SEQUENCE MyIdSequence START = 3;

DESCRIBE SEQUENCE MyIdSequence;