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

You can't but I don't think you miss anything.

Because you call sqlite always in process, it almost doesn't matter in performance whether you execute 1 insert statement or 100 insert statements. The commit however takes a lot of time so put those 100 inserts inside a transaction.

Sqlite is much faster when you use parameterized queries (far less parsing needed) so I wouldn't concatenate big statements like this:

insert into mytable (col1, col2)
select 'a','b'
union 
select 'c','d'
union ...

They need to be parsed again and again because every concatenated statement is different.

Answer 2

Yes it is possible, but not with the usual comma-separated insert values.

Try this...

insert into myTable (col1,col2) 
     select aValue as col1,anotherValue as col2 
     union select moreValue,evenMoreValue 
     union...

Yes, it's a little ugly but easy enough to automate the generation of the statement from a set of values. Also, it appears you only need to declare the column names in the first select.

Answer 3

Alex is correct: the "select ... union" statement will lose the ordering which is very important for some users. Even when you insert in a specific order, sqlite changes things so prefer to use transactions if insert ordering is important.

create table t_example (qid int not null, primary key (qid));
begin transaction;
insert into "t_example" (qid) values (8);
insert into "t_example" (qid) values (4);
insert into "t_example" (qid) values (9);
end transaction;    

select rowid,* from t_example;
1|8
2|4
3|9

Answer 4

As of version 3.7.11 SQLite does support multi-row-insert. Richard Hipp comments:

I'm using 3.6.13

I command like this:

insert into xtable(f1,f2,f3) select v1 as f1, v2 as f2, v3 as f3 
union select nextV1+, nextV2+, nextV3+

With 50 records inserted at a time, it takes only a second or less.

It's true using sqlite to insert multiple rows at a time is very possible. By @Andy wrote.

thanks Andy +1

Answer 5

If you use the Sqlite manager firefox plugin, it supports bulk inserts from INSERT SQL statements.

Infact it doesn't support this, but Sqlite Browser does (works on Windows, OS X, Linux)

Answer 6

update

As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases.

original answer

If I had privileges, I would bump andy's reply: You can insert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:

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

This can be recast into SQLite as:

     INSERT INTO 'tablename'
          SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'

a note on performance

I originally used this technique to efficiently load large datasets from Ruby on Rails. However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTs within a single transaction:

BEGIN TRANSACTION;
INSERT INTO 'tablename' table VALUES ('data1', 'data2');
INSERT INTO 'tablename' table VALUES ('data3', 'data4');
...
COMMIT;

If efficiency is your goal, you should try this first.

a note on UNION vs UNION ALL

As several people commented, if you use UNION ALL (as shown above), all rows will be inserted, so in this case, you'd get four rows of data1, data2. If you omit the ALL, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). We're using UNION ALL since it more closely matches the semantics of the original post.

in closing

P.S.: Please +1 andy's reply, not mine! He presented the solution first.