Is it possible to insert multiple rows at a time i

Question

In MySQL you can insert multiple rows like this:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2');

However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax to do that?

Answer 1

I wrote some ruby code to generate a single 500 element multi-row insert from a series of insert statements which was considerably faster than running the individual inserts. Then I tried simply wrapping the multiple inserts into a single transaction and found that I could get the same kind of speed up with considerably less code.

BEGIN TRANSACTION;
INSERT INTO table VALUES (1,1,1,1);
INSERT INTO table VALUES (2,2,2,2);
...
COMMIT;

Answer 2

I have a query like below, but with ODBC driver SQLite has an error with "," it says. I run vbscript in HTA (Html Application).

INSERT INTO evrak_ilac_iliskileri (evrak_id, ilac_id, baglayan_kullanici_id, tarih) VALUES (4150,762,1,datetime()),(4150,9770,1,datetime()),(4150,6609,1,datetime()),(4150,3628,1,datetime()),(4150,9422,1,datetime())

Answer 3

Yes, as of SQLite 3.7.11 this is supported in SQLite. From the SQLite documentation:

(when this answer was originally written, this was not supported)

For compatibility with older versions of SQLite, you can use the trick suggested by andy and fearless_fool using UNION, but for 3.7.11 and later the simpler syntax described in here should be preferred.

Answer 4

Yes, sql can do this, but with a different syntax. The sqlite documentation is pretty good, by the way. It will also tell you that the only way to insert several row is use a select statement as the source of the data to be inserted.

Answer 5

The problem with using transaction is that you lock the table also for reading. So if you have really much data to insert and you need to access to your data, for exemple a preview or so, this way doesn't work well.

The problem with the other solution is that you lose the order of the inserting

insert into mytable (col)
select 'c'
union 
select 'd'
union 
select 'a'
union 
select 'b';

In the sqlite the data will be store a,b,c,d...

Answer 6

If you are using bash shell you can use this:

time bash -c $'
FILE=/dev/shm/test.db
sqlite3 $FILE "create table if not exists tab(id int);"
sqlite3 $FILE "insert into tab values (1),(2)"
for i in 1 2 3 4; do sqlite3 $FILE "INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5"; done; 
sqlite3 $FILE "select count(*) from tab;"'

Or if you are in sqlite CLI, then you need to do this:

create table if not exists tab(id int);"
insert into tab values (1),(2);
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
select count(*) from tab;

How does it work? It makes use of that if table tab:

id int
------
1
2

then select a.id, b.id from tab a, tab b returns

a.id int | b.id int
------------------
    1    | 1
    2    | 1
    1    | 2
    2    | 2

and so on. After first execution we insert 2 rows, then 2^3=8. (three because we have tab a, tab b, tab c)

After second execution we insert additional (2+8)^3=1000 rows

Aftern thrid we insert about max(1000^3, 5e5)=500000 rows and so on...

This is the fastest known for me method of populating SQLite database.