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?
Best Answer
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 river's reply: You can insert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:
This can be recast into SQLite as:
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: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 ofdata1, data2
. If you omit theALL
, 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 river's reply, as it presented the solution first.