Insert multiple records in Sqflite

Question

How to insert quickly multiple records in sqflite? The standard quickly method is:

await database.insert(table, object.toMap())

But I don't think that insert record one to one with a cycle is a good idea. Or I can insert all list with a transaction?

Answer 1

As I mentioned in the comment, you can use Batch. Here is the sample.

Batch batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);

Now if you are looking for result (it will cost you some memory), you use

results = await batch.commit();

And if you are looking for fast performance, just ignore the result and use

await batch.commit(noResult: true);

Source

Answer 2

You can use batch in such case.

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();

big batches,you can use await batch.commit(noResult: true);

Answer 3

I saw the possibility of using database transactions, very similar to Android. I find it better to use them, here I show an example of code if it could be useful to someone. In my example try-catches are shown, but it is possible to use Future function callbacks to get a cleaner code :

Future<List<dynamic>> insertAll(String table, List<dynamic> objects) async {
List<dynamic> listRes = new List();
var res;
try {
  await DbHelper().database.transaction((db) async {
    objects.forEach((obj) async {
      try {
        var iRes = await db.insert(table, obj.toMap());
        listRes.add(iRes);
      } catch (ex) {
        DbHelper().databaseLog(CON_INSERT_MULTIPLE, "Error!", ex);
      }
    });
  });
  DbHelper().databaseLog(CON_INSERT_MULTIPLE, table, listRes);
  res = listRes;
} catch (er) {
  res = OutComeCallClient.ERROR;
  DbHelper().databaseLog(CON_INSERT_MULTIPLE, "Error!", er);
}
return res;
}

Answer 4

If you have a list of objects you want to insert, you can do it like this (it's not a very clean code, but it works):

  insertClients(List<Client> clients) async {
    final db = await database;
    var buffer = new StringBuffer();
    clients.forEach((c) {
      if (buffer.isNotEmpty) {
        buffer.write(",\n");
      }
      buffer.write("('");
      buffer.write(c.firstName);
      buffer.write("', '");
      buffer.write(c.lastName);
      buffer.write("', '");
      buffer.write(c.address);
      buffer.write("')");
    });
    var raw =
        await db.rawInsert("INSERT Into Clients (firstName,lastName,address)"
            " VALUES ${buffer.toString()}");
    return raw;
  }

I'm using this statement from SQLite:

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