How do i insert 1000 times in one statement? with SQLITE?

-edit- to make it more clear, i was just using a cmd line (actually ide in this case) and wanted to do quick testing with ram and didnt feel like making a full blown prj for a quick throwaway test.

I开发者_Go百科 want to fill this table with 10000000 values but first i want only 1000.

I tried this in sqlite database browser but 3 isnt inserted unless i drop everything after it. But more importantly i dont know how to have num go from 1 to 1000.

create table if not exists test1(id integer primary key, val integer);
insert into test1(val) select '3' as num where num between 1 and 1000

OK, here's a way to do it in pure SQL...

create table if not exists test1(id integer primary key, val integer);

create trigger test1_ins_trigger after insert on test1
  when new.val < 1000 begin
    insert into test1(val) values(new.val + 1);
  end;

pragma recursive_triggers = 1;

insert into test1(val) values(1);

CREATE TEMP TABLE Bits (Bit INTEGER PRIMARY KEY);
INSERT INTO Bits VALUES (0);
INSERT INTO Bits VALUES (1);

CREATE TEMP TABLE Nums AS SELECT
     b9.Bit * 512 + b8.Bit * 256 + b7.Bit * 128 + b6.Bit * 64 + b5.Bit * 32 +
     b4.Bit * 16 + b3.Bit * 8 + b2.Bit * 4 + b1.Bit * 2 + b0.Bit
     AS Num
FROM Bits b9, Bits b8, Bits b7, Bits b6, Bits b5,
     Bits b4, Bits b3, Bits b2, Bits b1, Bits b0;

CREATE TABLE Test1 (ID INTEGER PRIMARY KEY, Val INTEGER);
INSERT INTO Test1 SELECT Num, 3 FROM Nums WHERE Num BETWEEN 1 AND 1000;

DROP TABLE IF EXISTS test1;
CREATE TABLE IF NOT EXISTS test1(id INTEGER PRIMARY key, val INTEGER);
WITH RECURSIVE cnt(x) AS 
(
   SELECT
      1 
   UNION ALL
   SELECT
      x + 1 
   FROM
      cnt LIMIT 1000000 
)
INSERT INTO
   test1(val) 
   SELECT
      x AS outased 
   FROM
      cnt;

Is there any requirement for this to be pure SQLite? If not, simply loop in some programming language (php/perl/pick your poison) and create INSERT statements in a loop.

For a uniquely SQLite specific solution, you can use virtual tables and create a module to implement 1..1000 virtual table.