CODEWITHSUNDEEP
snowflake-cloud-data-platformdbt
chapsac
chapsac

Reputation: 37

Snowflake while loop

I am working on migrating SQL Server stored procedure 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;
$$
;

Upvotes: 2

Views: 5925

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

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 :=.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

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;
$$

Upvotes: 1

Related Questions