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, Mar 15 '14 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, Mar 15 '14 at 18:16

score 12 · Accepted Answer · edited May 23 '17 at 12:10

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 '17 at 12:10

Community

1
1

answered Mar 15 '14 at 18:15

piotrpo

12,398
7
42
58

Is there any alternative methods using myDB.execSQL? – mridul Mar 15 '14 at 18:19
1

u can use myDb.insert method – Kapil Vats Mar 15 '14 at 18:28
@piotrpo Your 1st answer is work. how to use your 2nd answer? – mridul Mar 15 '14 at 18:34
5

Note: You need to call `myDb.setTransactionSuccessful();` before `endTransaction()` in order for the changes to be committed. – Alicia May 30 '15 at 13:43

score 5 · Answer 2 · answered Mar 15 '14 at 18:13

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 '14 at 18:13

Kapil Vats

5,485
1
27
30

which version you are testing? yes i m pretty sure. – Kapil Vats Mar 15 '14 at 18:27
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 Mar 15 '14 at 18:30
to find out the sqlite version you can use `adb shell sqlite3 --version` – Kapil Vats Mar 15 '14 at 18:31
4.0.3 android version – mridul Mar 15 '14 at 18:39

Android : Insert multiple rows into sqlite database not working

2 Answers2

Linked

Related