CODEWITHSUNDEEP
androidsqlite
mridul
mridul

Reputation: 2096

Android : Insert multiple rows into sqlite database not working

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?

Upvotes: 6

Views: 14027

Answers (2)

piotrpo
piotrpo

Reputation: 12636

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?

Upvotes: 12

Kapil Vats
Kapil Vats

Reputation: 5515

Multiple rows insert will work only in SQLite 3.7.11 or above, but only jellybean and kitkat supports Sqlite 3.7.11

Upvotes: 5

Related Questions