Bulk Insertion on Android device

Question

I want to bulk insert about 700 records into the Android database on my next upgrade. What's the most efficient way to do this? From various posts, I know that if I use Insert statements, I should wrap them in a transaction. There's also a post about using your own database, but I need this data to go into my app's standard Android database. Note that this would only be done once per device.

Some ideas:

1. Put a bunch of SQL statements in a file, read them in a line at a time, and exec the SQL.

2. Put the data in a CSV file, or JSON, or YAML, or XML, or whatever. Read a line at a time and do db.insert().

3. Figure out how to do an import and do a single import of the entire file.

4. Make a sqlite database containing all the records, copy that onto the Android device, and somehow merge the two databases.

5. [EDIT] Put all the SQL statements in a single file in res/values as one big string. Then read them a line at a time and exec the SQL.

What's the best way? Are there other ways to load data? Are 3 and 4 even possible?

Answer 1

I don't believe there is any feasible way to accomplish #3 or #4 on your list.

Of the other solutions you list two that have the datafile contain direct SQL, and the other has the data in a non-SQL format.

All three would work just fine, but the latter suggestion of grabbing the data from a formatted file and building the SQL yourself seems the cleanest. If true batch update capability is added at a later date your datafile is still usable, or at least easily processable into a usable form. Also, creation of the datafile is more straightforward and less error prone. Finally, having the "raw" data would allow import into other data-store formats.

In any case, you should (as you mentioned) wrap the groups of inserts into transactions to avoid the per-row transaction journal creation.

Answer 2

I'm not sure if this is what you meant to describe in your 1st and 2nd option. But there is no need to do multiple inserts to insert multiple records. Couldn't you just use the execSQL(String sql) method on your SQLiteDatabase object. The SQL code for inserting several records in the same query is like this:

INSERT INTO MyTable (nameOfFirstFieldInt, nameOfSecondFieldInt, nameOfThirdFieldString)
VALUES (0, 1, 'Some record'),
(1, 2, 'Another record'),
(3, 5, 'Even a third one...'),
(8, 13, 'What to say here?');

You could store all the values you need either in a text file you distribute with your app, or inside your app somewhere (not sure what would be the best place, the res file or somewhere else?), loop over them one at a time to construct the above query, and execute. And since it is done as one single query, you shouldn't have to worry about transactions and such.