nishant pathak nishant pathak - 25 days ago 14
Android Question

How to do bulk insertion in square sqldelight?

Thanks square for SQLDelight and providing typesafe api's for sqlite db interactions. I am doing single insert row as below:

MyTable.Insert_row insert_row =
new MyTableModel.Insert_row(
mOpenHelper.getWritableDatabase(),
MyTable.FACTORY
);
MyTable.bind(insert_row, data);
insert_row.program.executeInsert();


Is there a way to perform bulk insertion for list of data at once ?

Answer

Kind of. Theres two approaches. Since SQLite 3.7.11 (included in Android API 16 and above) you can insert multiple values at once from the sqlite side:

INSERT INTO myTable
VALUES (?, ?), (?, ?), (?, ?);

which is probably not what you want. As long as you are only creating the insert statement once, binding and executing multiple rows is actually really fast:

MyTable.Insert_row insert_row =
    new MyTableModel.Insert_row(
      mOpenHelper.getWritableDatabase(),
      MyTable.FACTORY
    );
for (Row row : rows) {
  MyTable.bind(insert_row, row);
  insert_row.program.executeInsert();
}

which is probably closer to what you want. At the moment there's no planned support of arbitrary-sized bulk inserts from the .sq file side.