sql - 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>