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
– Simon, Commented Nov 16, 2013 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
– AllOutOfSalt, Commented Nov 16, 2013 at 11:47

Szymon · Accepted Answer · 2013-11-16 11:38:07Z

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, 2013 at 11:38

Szymon

43k16 gold badges99 silver badges115 bronze badges

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

5 Comments

AllOutOfSalt Over a year ago

Would it be able to handle 800 insert statements?

Szymon Over a year ago

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.

Ricardo Over a year ago

You don't have to call insert every time. You can pass several values to one insert clause. Have a look here: stackoverflow.com/questions/1609637/…

Szymon Over a year ago

@Ricardo That's even better!

Janeks Bergs Over a year ago

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.

Do Xuan Nguyen · Accepted Answer · 2017-06-13 04:44:04Z

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);
}

Collectives™ on Stack Overflow

multiple insert statements android sqlite

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related