Insert Multiple Rows in SQLite

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.

SQL logic error or missing database -error on insert of multiple rows

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.

Is it possible to insert multiple rows in a table based on a select returning more than one row

You can use a const in the select list

INSERT INTO A(colA, colB)
SELECT id, 'Hardcoded-Value'
FROM B
WHERE status = 'APPROVED'

Insert multiple values with the same foreign key

You can use a CROSS join of the id that you get from the new room to a CTE that returns the items that you want to insert:

WITH cte(item_name) AS (VALUES ('Chair'), ('TV'), ('Carpet'))
INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r CROSS JOIN cte c
WHERE r.room_name = 'Living Room';

See the demo.

If you are using a version of SQLite that does not support CTEs use UNION ALL in a subquery:

INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r 
CROSS JOIN (
  SELECT 'Chair' item_name UNION ALL 
  SELECT 'TV' UNION ALL
  SELECT 'Carpet'
) c
WHERE r.room_name = 'Living Room';

See the demo.

Limit on multiple rows insert

SQLite handles a multi-row INSERT like a compound SELECT.
The limit for that is indeed 500.

However, since version 3.8.8,

the number of rows in a VALUES clause is no longer limited by SQLITE_LIMIT_COMPOUND_SELECT.

---

Related Topics