Snowflake while loop

user_17908002 0
Viewed 924

I am working on migrating SQL server store proc and loading the data to Snowflake using DBT. I am having some issues with using insert within while loop. Would really appreciate if anyone has feedbacks on this.

Error: SQL Compilation error: syntax line 7 position 18 unexpected '('. syntax error line 8 at position 12 unexpected 'insert'.

Temp tables are defined already.

execute immediate $$ 
begin 
 set firstmonth = '2022-03-01'; 
 set lastmonth = '2022-04-01'; 
 set currmonth = $firstmonth;
 
 while ($currmonth <$lastmonth) do   
   insert into tmptable( col1, col2)  
   select 1,2 from tableA;
   currmonth = dateadd(month, 1,$currmonth);
 end while;
end;
$$
;
dbt snowflake-cloud-data-platform
2 Answers

The syntax needed is slightly different, fixed:

execute immediate $$ 
declare
 firstmonth date default '2022-03-01';
 lastmonth date default '2022-04-01';
 currmonth date default firstmonth;

begin 
 while (currmonth < lastmonth) do   
   insert into tmptable( col1, col2)  
   select 1,2 from tableA;
   currmonth := dateadd(month, 1, currmonth);
 end while;
return currmonth;
end;
$$
user_1324380

One way to trasnalte the code is to use Snowflake Scripting variables(LET) instead of session variables(SET):

begin 
 let firstmonth DATE := '2022-03-01'; 
 let lastmonth DATE := '2022-04-01'; 
 let currmonth DATE := :firstmonth;
 
 while (currmonth <lastmonth) do   
   insert into tmptable( col1, col2)  
   select 1,2 from tableA;
   currmonth := dateadd(month, 1,currmonth);
 end while;
end;

The assignment operator for Snowflake Scripting is :=.

user_50708790
Related