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?

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

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

Solution 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