How to insert multiple rows into a SQLite 3 table?

Question

In MySQL I'd use

INSERT INTO `mytable` (`col1`, `col2`) VALUES
  (1, 'aaa'),
  (2, 'bbb');

but this causes an error in SQLite. What is the correct syntax for SQLite?

possible duplicate of [Is it possible to insert multiple rows at a time in an SQLite database?](http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) — , Apr 06 '12 at 03:47
As long as all inserts **run in the same transaction**, it will be very fast. If using a *prepared statement* the parsing/prep overhead that needs to be done by SQLite will be minimized. So ... it's not really needed and makes more sense in an engine that has batch triggers, etc. — , Apr 06 '12 at 03:47
Possible duplicate of [Is it possible to insert multiple rows at a time in an SQLite database?](https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) — StayOnTarget, Aug 16 '19 at 15:46

score 15 · Accepted Answer · edited May 23 '17 at 11:59

15

To answer your comment to OMG Ponies answer:

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

"The new multi-valued insert is merely syntactic suger (sic) for the compound insert. 
There is no performance advantage one way or the other."

edited May 23 '17 at 11:59

Community

answered Apr 06 '12 at 03:34

Leo Correa

score 8 · Answer 2 · answered Apr 06 '12 at 03:25

8

Use a UNION:

INSERT INTO `mytable` 
 (`col1`, `col2`) 
SELECT 1, 'aaa'
UNION ALL
SELECT 2, 'bbb'

UNION ALL is quicker than UNION, because UNION removes duplicates -- UNION ALL does not.

answered Apr 06 '12 at 03:25

OMG Ponies

Do you mean this can be any better than inserting every row with a separate INSERT? Using the example I've shown with MySQL can actually speed imports up dramatically as compared to using individual inserts for every record. – Ivan Apr 06 '12 at 03:28
@Ivan: Yes, because INSERT writes to the transaction log (depending on logging level, but if you want point in time restoration...). So a single insert (or update for that matter) statement can be much quicker. For SQL Server, I've heard some say you 10-20 UNION's, maximum. – OMG Ponies Apr 06 '12 at 04:47

score 6 · Answer 3 · answered May 16 '13 at 16:16

Start from version 2012-03-20 (3.7.11), sqlite support the following INSERT syntax:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');

SQLite INSERT Statement Diagram

3 Answers3