How to Insert Multiple Rows At a Time in an Sqlite Database

Is it possible to insert multiple rows at a time in an SQLite database?

update

As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases.

original answer

If I had privileges, I would bump river's reply: You can insert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:

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

This can be recast into SQLite as:

     INSERT INTO 'tablename'
          SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'

a note on performance

I originally used this technique to efficiently load large datasets from Ruby on Rails. However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTs within a single transaction:

BEGIN TRANSACTION;
INSERT INTO 'tablename' table VALUES ('data1', 'data2');
INSERT INTO 'tablename' table VALUES ('data3', 'data4');
...
COMMIT;

If efficiency is your goal, you should try this first.

a note on UNION vs UNION ALL

As several people commented, if you use UNION ALL (as shown above), all rows will be inserted, so in this case, you'd get four rows of data1, data2. If you omit the ALL, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). We're using UNION ALL since it more closely matches the semantics of the original post.

in closing

P.S.: Please +1 river's reply, as it presented the solution first.

Insert multiple rows in SQLite

Since you mention latest version of SQLite, you should use multi-valued insert (supported by SQLite since version 3.7.11), like this:

INSERT INTO mytable (col1, col2, col3) VALUES
    (1, 2, "abc"),
    (2, 4, "xyz"),
    (3, 5, "aaa"),
    (4, 7, "bbb");

This is shorter, faster and less prone to errors. This syntax is also supported by some other databases (at least MySQL and PostgreSQL).

how to insert multiple rows into sqlite android

A better structure would be create a db reference outside the method, and pass it as a reference:

SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();

// your for loop

db.setTransactionSuccessful();
db.endTransaction();

==============
Please check below which is used for insert multi rows:

// adb is SQLiteOpenHelper
JSONObject jsonObject = new JSONObject(response);
JSONArray foodsessions = jsonObject.getJSONArray("foodsessions");
int length = foodsessions.length();

for (int i = 0; i < length; i++) {
    JSONObject o = foodsessions.getJSONObject(i);
    String session = session_object.getString("sessionname");
    String start_time = session_object.getString("start_time");
    String end_time = session_object.getString("end_time");
    String session_id = session_object.getString("id");    

    SQLiteDatabase db = adb.getWritableDatabase();

    ContentValues newValues = new ContentValues();
    newValues.put(adb.ATTRIBUTE_session, session);
    newValues.put(adb.ATTRIBUTE_start_time, start_time);
    newValues.put(adb.ATTRIBUTE_end_time, end_time);
    newValues.put(adb.ATTRIBUTE_session_id, session_id);

    long res = db.insertWithOnConflict(adb.TABLE_NAME, null, newValues, SQLiteDatabase.CONFLICT_REPLACE);
    db.close();
}

A better solution is avoiding to use raw query if it can be done by the method provided by SQLiteDatabase.

How to insert multiple rows into a SQLite 3 table?

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

SQLITE Insert Multiple Rows Using Select as Value

INSERT INTO queue (TransKey, CreateDateTime, Transmitted) 
  SELECT Id, '2013-12-19T19:47:33',  0 
  FROM trans WHERE Id != (SELECT TransKey from queue)

There are two different "flavors" of INSERT. The one you're using (VALUES) inserts one or more rows that you "create" in the INSERT statement itself. The other flavor (SELECT) inserts a variable number of rows that are retrieved from one or more other tables in the database.

While it's not immediately obvious, the SELECT version allows you to include expressions and simple constants -- as long as the number of columns lines up with the number of columns you're inserting, the statement will work (in other databases, the types of the values must match the column types as well).

---

Related Topics