SQLite loop statements?

Question

Is there any loop statements in SQLite like FOR .. in .. LOOP or something like that? I have two columns StartRange, EndRange and I need to insert whole sequence in other table. So if StartRange is 1 and EndRange is 3 it's necessary to make three inserts with value, contains 1, 2, 3.

Answer 1

You can make loops in SQL with recursive triggers. Using mu is too short's schema

sqlite> create table t (startrange int not null, endrange int not null); sqlite> insert into t values(1, 3); sqlite> create table target (i int not null); 

we need to enable recursive triggers in SQLite:

sqlite> PRAGMA recursive_triggers = on; 

Make a temporary trigger to loop up to the end of the range:

sqlite> create temp trigger ttrig    ...> before insert on target    ...> when new.i < (select t.endrange from t) begin    ...> insert into target values (new.i + 1);    ...> end; 

Kick it off:

sqlite> insert into target values ((select t.startrange from t)); sqlite> select * from target; 3 2 1 sqlite>