Is it possible to insert multiple rows at a time i

Question

In MySQL you can insert multiple rows like this:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2');

However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax to do that?

Answer 1

According to this page it is not supported:

• 2007-12-03 : Multi-row INSERT a.k.a. compound INSERT not supported.

  INSERT INTO table (col1, col2) VALUES 
      ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ...

Actually, according to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows: VALUES 'john', 'mary', 'paul';

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

Answer 2

in mysql lite you cannot insert multiple values, but you can save time by opening connection only one time and then doing all insertions and then closing connection. It saves a lot of time

Answer 3

INSERT INTO TABLE_NAME 
            (DATA1, 
             DATA2) 
VALUES      (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2); 

Answer 4

you can use InsertHelper, it is easy and fast

documentation: http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html

tutorial: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/

Edit: InsertHelper is deprecated as of API Level 17

Answer 5

As the other posters have said, SQLite does not support this syntax. I don't know if compound INSERTs are part of the SQL standard, but in my experience they're not implemented in many products.

As an aside, you should be aware that INSERT performance in SQLite is improved considerably if you wrap multiple INSERTs in an explicit transaction.

Answer 6

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

PS: Please +1 to Brian Campbell's reply/answer. not mine! He presented the solution first.