How to insert multiple rows into a SQLite 3 table? [duplicate]

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?

Gemeinschaft

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. – Acetylide

Answer 1

This has already been answered before here: Is it possible to insert multiple rows at a time in an SQLite database?

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."

Raffin

Answer 2

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.

Deniable

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. – Gemeinschaft

@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. – Deniable

Answer 3

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');

Read documentation: http://www.sqlite.org/lang_insert.html

Affranchise