Best way to insert 200+ rows into SQLite Database

Question

I am creating a application with a database. I have one table that will have over 200 rows in it. (reference table used within the app) I have created a class to create database and upgrade it.

I have created strings within the class to create the tables required but adding 200+ rows into the class it will get very large

Is there a better way of inserting these rows. Currently they are in a SQL file within the application

Thanks

Are some of the rows repetitions or regular in some way? If so you could use a loop. Otherwize, I don't see how to create a 200+ row SQLite table without a 200+ line SQL file. — Zhehao Mao, Jun 22 '11 at 15:04
Going down the SQL file route. What would I need to change to make database read the file instead of making a String — James Dudley, Jun 22 '11 at 15:26
You mean have SQLite read the file without creating a Java String as an intermediate? I'm not quite sure. — Zhehao Mao, Jun 22 '11 at 15:30

score 0 · Answer 1 · answered Jun 22 '11 at 15:18

You should consider the use of transaction and maybe change the PRAGMA synchronous of the database. Inserting 200+ rows will be long , the use of transaction will reduce the time needed :

sql.execSQL("BEGIN;");
//insert 1
//insert 2
// ...
sql.execSQL("END;");

Changing PRAGMA synchronous for 0 or 1 will also increase the insertion speed but could induce some error (it remove the wrting lock while inserting)

See http://www.sqlite.org/pragma.html and http://www.sqlite.org/lang_transaction.html for more information

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

0

You could also ship with a pre-populated SQLite database in your assets folder like described in this SO answer:

Ship an application with a database

edited May 23 '17 at 12:27

Community

1
1

answered Jun 22 '11 at 15:22

Pedantic

5,032
2
24
37

score 0 · Answer 3 · answered Jun 22 '11 at 15:31

You could create a small .net application to populate the table when needed. Here is an example in vb. It can be easily translated into c# by googling vb to C# converter. Just copy and paste the code into the converter.

    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand
    Dim i as Integer 

    'Connect to database
    myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=yourdb") 
    myConnection.Open()

    'Insert rows, you may be able to use a for loop to make inserting easier
    myCommand = New SqlCommand("INSERT INTO yourtable VALUES( 12, _
    'IT Manager')",100,300,myConnection)
    i=myCommand.ExecuteNonQuery()

    'Close the connection

    myConnection.Close()

Best way to insert 200+ rows into SQLite Database

3 Answers3