8

In my Android SQLite databese query I have an INSERT INTO statement followed by about 600 ('data1'),('data2')... tags, generated by code.

After db.exec(sql) I got this error: "too many terms in compound SELECT (code1); while compiling INSERT INTO.. "

Is there any way to increase this limitation?

user3864532
  • 91
  • 1
  • 2
  • Can you paste your code, where you insert those records. – mata Aug 12 '14 at 06:43
  • 2
    You need to reduce the number of terms in your SELECT statement, break your insertion into parts with about 500 records – Farrokh Aug 12 '14 at 06:44
  • Instead of calling db.exec just once, do it for every record. – mata Aug 12 '14 at 06:46
  • public static void insertTableData(String[] values, int index){ // values are given as (valu1,value2,...) sql="INSERT INTO table (field1,field2...) VALUES "; for (int i=0;i – user3864532 Aug 12 '14 at 06:58

1 Answers1

9

The limit SQLITE_MAX_COMPOUND_SELECT cannot be raised at runtime,

So you need to split your inserts into batches of 500 rows each. This will be more efficient than inserting one row per query. For e.g.

BEGIN TRANSACTION
INSERT INTO tablename (data1,data2) VALUES ("data1","data2")
INSERT INTO tablename (data1,data2) VALUES ("data1","data2")
INSERT INTO tablename (data1,data2) VALUES ("data1","data2")
...
END TRANSACTION

Also see Insert Multiple Rows in SQLite

Community
  • 1
  • 1
Giru Bhai
  • 14,370
  • 5
  • 46
  • 74