How to insert multiple rows into a SQLite 3 table?

Question

In MySQL, I would 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?

Answer 1

This has already been said: Is it possible to insert multiple rows into a SQLite database at once?

To answer your OMG Ponies comment, reply:

Starting with version 3.7.11, SQLite supports multi-row insertion. Richard Hipp:

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

Answer 2

Use UNION:

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

UNION ALL faster than UNION because UNION removes duplicates - UNION ALL does not.

Answer 3

Starting with version 2012-03-20 (3.7.11), sqlite supports the following INSERT syntax:

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

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