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?

@RanjitPati myDB.execSQL("CREATE TABLE IF NOT EXISTS " + Buss + " (BussName VARCHAR(30),RouteName VARCHAR(30));"); — mridul
– mridul, Commented Mar 15, 2014 at 18:10
@unluddite you mean BussName, RouteName to 'BussName', 'RouteName' ? I try it it does not work. I can't find any other differents.. Please help if you can find it.. — mridul
– mridul, Commented Mar 15, 2014 at 18:16

Community · Accepted Answer · 2017-05-23 12:10:42Z

12

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?

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 15, 2014 at 18:15

piotrpo

12.7k8 gold badges44 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mridul Over a year ago

Is there any alternative methods using myDB.execSQL?

Kapil Vats Over a year ago

u can use myDb.insert method

mridul Over a year ago

@piotrpo Your 1st answer is work. how to use your 2nd answer?

Alicia Over a year ago

Note: You need to call myDb.setTransactionSuccessful(); before endTransaction() in order for the changes to be committed.

Kapil Vats · Accepted Answer · 2014-03-15 18:13:37Z

5

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

answered Mar 15, 2014 at 18:13

Kapil Vats

5,5151 gold badge29 silver badges30 bronze badges

4 Comments

Kapil Vats Over a year ago

which version you are testing? yes i m pretty sure.

Kapil Vats Over a year ago

you are testing on emulator? check which version on android its using, and if you testing on device, go to setting and see the android version

Kapil Vats Over a year ago

to find out the sqlite version you can use adb shell sqlite3 --version

mridul Over a year ago

4.0.3 android version

Collectives™ on Stack Overflow

Android : Insert multiple rows into sqlite database not working

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related