multiple insert statements android sqlite

Question

I have 800 insert statements downloaded from network in the following format:

insert into mytable(col1,..,coln) values(val1,..valn);insert into mytable col...

I have heard of transactions ,ContentValues , and using union but I can't figure out which one is better for this case.

What is the best way to insert them in sqlite database after truncating mytable?

First things first. This is not a good way of updating a table from data downloaded from a server. Why are you downloading insert statements? — Simon, Nov 16 '13 at 11:38
@Simon It's a local server and is not connected to the internet. I'm trying to insert the values of a mysql table (used as a backup table) into a sqlite's table (insert statements are created using a php script) — AllOutOfSalt, Nov 16 '13 at 11:47

score 19 · Accepted Answer · answered Nov 16 '13 at 11:38

19

In case of multiple queries to run, in order to improve data integrity and performance, you should use transactions. This is a code to give you an idea how to do it:

    SQLiteDatabase db = sqlHelper.getWritableDatabase(); // get a writable database here
    db.beginTransaction();

    try {
        for (int insertQuery : listOfQueries) {  // loop through your records 
            db.insert(...);
        }

        db.setTransactionSuccessful();
    }
    finally {
        db.endTransaction();
    }

    db.close();

answered Nov 16 '13 at 11:38

Szymon

42,577
16
96
114

1

Would it be able to handle 800 insert statements? – AllOutOfSalt Nov 16 '13 at 11:48
1

Yes, I do about 7000 in my app and works well. You should do it on a separate thread, best using `AsyncTask` though to avoid blocking the UI thread. – Szymon Nov 16 '13 at 11:51
3

You don't have to call `insert` every time. You can pass several `values` to one insert clause. Have a look here: http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database/1609688#1609688 – Ricardo Nov 16 '13 at 12:36
@Ricardo That's even better! – Szymon Nov 16 '13 at 19:01
Use `db.insertOrThrow` instead of just `db.insert` ! This code will never throw an exception so if one of your inserts fails you will have inserted incomplete data. – Janeks Bergs Jun 17 '18 at 18:35

score 0 · Answer 2 · answered Jun 13 '17 at 04:44

I use code below, run once only query.

 public void addListRecord(List<Province> list) {
    SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
    try {
        String sql = " INSERT INTO " + TABLE_PROVINCE + " ("
                + COL_PROVINCE_ID + ","
                + COL_PROVINCE_NAME + ","
                + COL_PROVINCE_STATUS + ") VALUES ";
        String value = "";
        for (Province item : list) {
            value += "('" + item.getId() + "',"
                    + "'" + item.getName() + "',"
                    + "'" + item.getStatus() + "')";
            value += ",";
        }
        value = Utils.removeLastChar(value);
        value += ";";
        String mQuery = sql + value;
        Logger.debug("mQuery: " + mQuery);
        SQLiteStatement statement = db.compileStatement(mQuery);
        statement.execute();
    } catch (SQLException e) {
        Logger.debug("SQLException Error: " + e);
    } finally {
        DatabaseManager.getInstance().closeDatabase();
    }
}
public static String removeLastChar(String s) {
    if (s == null || s.length() == 0) {
        return s;
    }
    return s.substring(0, s.length() - 1);
}

multiple insert statements android sqlite

2 Answers2

Linked