Android : Insert multiple rows into sqlite database not working

Question

I am Inserting single row into database using following method for android project.

myDB.execSQL("INSERT INTO "
 + Buss
 + " (BussName, RouteName)"
 + " VALUES ('buss1', 'buss2');");

It work fine. And I see this link Inserting multiple rows in sqlite database. and I try this method(insert multiple rows) in my android project, but It does not work.

myDB.execSQL("INSERT INTO "
 + Buss
 + " (BussName, RouteName)"
 + " VALUES ('buss1', 'buss2'),('buss1', 'buss2'),('buss1', 'buss2');");

How to do it?

Answer 1

You need to call separate insert statement for each row.

For performance reason you can group every few calls (let say ~20) into one transaction:

myDb.beginTransaction();
   for(<your loop definition>){ myDb.execSQL(<your insert statement>) }
myDb.setTransactionSuccessful();
myDb.endTransaction();

The main idea is to not write physical database file on every inserted row, but every few rows. On other had as long as inserted data is not persisted on "drive" it's in the memory. For small data sets you can just start transaction, make all inserts and end transaction in one block.

For bigger data you should make your transactions smaller.

Using prepared statement instead of standard statement is also a good idea, as the SQL interpreter needs to parse query only once - more information can be found here: How do I use prepared statements in SQlite in Android?