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?

Ariose

There is a batch operation, did you try that? – Nigelniger

No, I haven't tried it yet. Do you have a small example? You can explain with an answer, thanks. – Ariose

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

Nigelniger

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);

Tsingyuan

Ok, in my ask I said if there is an quickly method how this: batch.insert(tableName, map); It is possible? – Ariose

Yes, it will work, If not take a look at this example he has put forth good examples. github.com/AndriousSolutions/dbutils/blob/… – Tsingyuan

Answer 3

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');

Villous

While I do prefer the batch solution, if you still want a single statement, you should use parameters for the content columns and build something like sql: INSERT INTO 'tablename' ('column1', 'column2') VALUES (?, ?), (?, ?), (?, ?); arguments: ['data1', 'data2', 'data1', 'data2','data1', 'data2']; – Woodward

As mentioned in comment above, there is an issue with this methodology. For more than 1000 contacts the sqflite will throw exception E/SQLiteLog(22159): (1) too many SQL variables – Pledge

Answer 4

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;
}

Ariose

Answer 5

And this is my code, maybe can help ;) Thanks to MihaiV.

insertOrUpdateAll(List<Map<String, dynamic>> ticks) async {

    if (ticks.length == 0) return null;
    final columns = ticks.first.keys.join(",");

    var valuesBuffer = new StringBuffer();
    ticks.forEach((task) {
      if (valuesBuffer.isNotEmpty) valuesBuffer.write(",\n");
      valuesBuffer.write("(");

      int ix = 0;
      task.forEach((key, value) {
        if (ix++ != 0) valuesBuffer.write(',');

        final isString =
            columnsInfo.where((c) => c.name == key).first.type == RowType.text;
        if (isString) valuesBuffer.write("'$value'");

        valuesBuffer.write(value);
      });

      valuesBuffer.write(")");
    });

    return await db.rawInsert("INSERT Into Clients ($columns)"
        " VALUES ${valuesBuffer.toString()}");
}

Saudra

There is an issue with this methodology. For more than 1000 contacts the sqflite will throw exception E/SQLiteLog(22159): (1) too many SQL variables – Pledge

Answer 6

   Model



 import 'package:sqflite/sqflite.dart';
    import 'package:path/path.dart';
    
    final String tableName = "todom";
    final String Column_id = "id";
    final String Column_name = "name";
    
    class TaskModel{
      final String name;
      int id;
    
      TaskModel({this.name, this.id});
    
      Map<String, dynamic> toMap(){
        return {
          Column_name : this.name
        };
      }
    }
    
    class TodoHelper{
      Database db;
    
      TodoHelper(){
        initDatabase();
      }
    
      Future<void> initDatabase() async{
        db = await openDatabase(
          join(await getDatabasesPath(), "databse.db"),
          onCreate: (db, version){
            return db.execute("CREATE TABLE $tableName($Column_id INTEGER PRIMARY KEY AUTOINCREMENT, $Column_name TEXT)");
          },
          version: 1
        );
      }
    
      Future<void> insertTask(TaskModel task) async{
        try{
          db.insert(tableName, task.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
        }catch(_){
          print(_);
        }
      }
    
      Future<List<TaskModel>> getAllTask () async{
        final List<Map<String, dynamic>> tasks = await db.query(tableName);
    
        return List.generate(tasks.length, (i){
          return TaskModel(name: tasks[i][Column_name], id: tasks[i][Column_id]);
        });
      }
    
    
    }




main.dart



import 'package:flutter/material.dart';

import 'TaskModel.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        // This is the theme of your application.
        //
        // Try running your application with "flutter run". You'll see the
        // application has a blue toolbar. Then, without quitting the app, try
        // changing the primarySwatch below to Colors.green and then invoke
        // "hot reload" (press "r" in the console where you ran "flutter run",
        // or simply save your changes to "hot reload" in a Flutter IDE).
        // Notice that the counter didn't reset back to zero; the application
        // is not restarted.
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(),
    );
  }
}

class MyHomePage extends StatefulWidget {
  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  final textController = TextEditingController();

  List<TaskModel> tasks = [];

  TaskModel currentTask;



  @override
  Widget build(BuildContext context) {
    final TodoHelper _todoHelper = TodoHelper();
    return Scaffold(
      body: Container(
        padding:EdgeInsets.all(32),
        child: Column(
          children: <Widget>[
            TextField(
              controller: textController,
            ),
            FlatButton(
              child: Text("Insert"),
              onPressed: (){
                currentTask = TaskModel(name: textController.text);
                _todoHelper.insertTask(currentTask);
              },
              color: Colors.blue,
              textColor: Colors.white,
            ),
            FlatButton(
              child: Text("Show All Task"),
              onPressed: () async{
                List<TaskModel> list = await _todoHelper.getAllTask();

                setState(() {
                  tasks = list;
                });
              },
              color: Colors.red,
              textColor: Colors.white,
            ),

            Expanded(
              child: ListView.separated(
                  itemBuilder: (context, index){
                    return ListTile(
                      leading: Text("${tasks[index].id}"),
                      title: Text("${tasks[index].name}"),
                    );
                  },
                  separatorBuilder: (context, index) => Divider(),
                  itemCount: tasks.length,

              ),
            )
          ],
        ),
      ),
    );
  }
}

Iow

Answer 7

you can try with rawInsert

Database db = await instance.database;
String sql = '''
INSERT INTO p_tasklistproviderobjective (
    idticket,
    objective
  ) VALUES (?, ?)
''';
//you can get this data from json object /API
List<Map> data = [
  {"idticket": 5, "objective": "dono"},
  {"idticket": 6, "objective": "dono2"},
  {"idticket": 7, "objective": "dono3"},
  {"idticket": 8, "objective": "dono4"},
];
//and then loop your data here
data.forEach((element) async {
  await db.rawInsert(sql, [element['idticket'], element['objective']]);
});

Wicker

Answer 8

SQFLite doesn't offer a way for bulk insertion. Even though they have batch concept you didn't get effective response.

For example I'm inserting 10 rows in a table one by one which taking 5 seconds Here I'm using for loop to iterate the insertion query one by one.

In the batch concept same example we will pass a list of 10rows inside of the batch transaction query it'll run a loop like we did in the traditional method So basically normal insertion taking 5 seconds then batch concept is taking 4 seconds like wise it'll work

So I prefer using Isolate concept to insert bulk data in short time.

Note : Isolate may cause memory issue if you use it in wrong place Read well the documentation before touch Isolate concept. Isolate is not suitable in all such cases.

Try using Isolates Refer the below blog

https://medium.com/flutter-community/isolates-in-flutter-a0dd7a18b7f6

Stirps

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Blackamoor