How to insert multiple rows into a SQLite 3 table?

问题:

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?

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

回答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.

回答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