SQLite loop statements?

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;321sqlite> 

Apparently the looping construct in SQLite is the WITH RECURSIVE clause.That documentation link has sample count-to-ten code, a Mandelbrot set plotter, and a Sudoku puzzle solver, all in pure SQL.Here's an SQLite query that computes the Fibonacci sequence to give you a feel for it:

sqlite> WITH RECURSIVE   ...>   fibo (curr, next)   ...> AS   ...>   ( SELECT 1,1   ...>     UNION ALL   ...>     SELECT next, curr+next FROM fibo   ...>     LIMIT 100 )   ...> SELECT group_concat(curr) FROM fibo;1,1,2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765,10946,...

And here's a Sieve of Eratosthenes:

begin transaction;drop table if exists naturals;create table naturals( n integer unique primary key asc,  isprime bool,  factor integer);with recursive  nn (n)as (  select 2  union all  select n+1 as newn from nn  where newn < 1e4)insert into naturalsselect n, 1, null from nn;insert or replace into naturals  with recursive    product (prime,composite)  as (    select n, n*n as sqr      from naturals      where sqr <= (select max(n) from naturals)    union all    select prime, composite+prime as prod    from      product    where      prod <= (select max(n) from naturals)  )select n, 0, primefrom product join naturals  on (product.composite = naturals.n);commit;

You can do this sort of thing in straight SQL if you have an extra table that holds all the integers that you need.

Suppose your StartRange and EndRange range between one and ten and you have a table like this:

sqlite> select i from ints;i1...10

This table simply contains all the possible integers that you need (i.e. one through ten).

Then if you also have this:

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

You can do your INSERTs into target with a join:

insert into target (i)select ints.ifrom ints join t on (ints.i >= t.startrange and ints.i <= t.endrange)

The result is this:

sqlite> select * from target;i123

Of course your real t would have more rows so you'd want a WHERE clause to limit which row of t you look at.

Similar things are often done with dates (look up "calendar tables").

So if your ranges are small (for some definition of small) then generate your ints table once, add an index to it, and use the above technique to do all the INSERTs right inside the database. Other databases have their own ways (such as PostgreSQL's generate_series) to do this sort of thing without need an explicit ints table but SQLite is (intentionally) limited.

SQL is generally set-based so loops aren't natural. What is natural is building the appropriate sets by describing what you need. OTOH, sometimes unnatural acts are necessary and sensible.

I don't know if this makes sense for your application, I just thought I'd demonstrate how it can be done. If this approach doesn't make sense in your case then you can generate a bunch of INSERT statements outside the database.