Merge Two Sqlite database & dump data from attached database (unable to open database)

Question

I have two databases Mydb.db and Updatedb.db. These have the same structure and both have an identical Table1.

I want to add all the records in Updatedb.db to Table1.

There are approx 25000 records to update.

I used:

mDataBase.openDataBaseForWrite();
    SQLiteDatabase myInternalDatabase = mDataBase.getDb();
    myInternalDatabase.execSQL("ATTACH DATABASE '" + ATTACH_DB_PATH
            + File.separator + ATTACH_DB_NAME + "' AS New_DB");

    myInternalDatabase
            .execSQL("INSERT INTO ResultMaster SELECT * FROM New_DB.ResultMaster;");

I get this exception:

09-16 14:44:32.584: E/AndroidRuntime(2766): FATAL EXCEPTION: main
09-16 14:44:32.584: E/AndroidRuntime(2766): android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database: /mnt/sdcard/AttachDb/backupname.db (code 14)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:727)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1665)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at com.attachtwodatabase.MainActivity.attachDatabase(MainActivity.java:62)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at com.attachtwodatabase.MainActivity.access$0(MainActivity.java:59)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at com.attachtwodatabase.MainActivity$1.onClick(MainActivity.java:49)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.view.View.performClick(View.java:4207)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.view.View$PerformClick.run(View.java:17372)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.os.Handler.handleCallback(Handler.java:725)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.os.Handler.dispatchMessage(Handler.java:92)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.os.Looper.loop(Looper.java:137)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at android.app.ActivityThread.main(ActivityThread.java:5041)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at java.lang.reflect.Method.invokeNative(Native Method)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at java.lang.reflect.Method.invoke(Method.java:511)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
09-16 14:44:32.584: E/AndroidRuntime(2766):     at dalvik.system.NativeStart.main(Native Method)

What am I doing wrong?

Answer 1

ATTACH the database to be merged to the target database insert records from the attached database to the target:

INSERT INTO table1 SELECT * FROM attacheddb.table1;

Do it e.g. in an asynctask to prevent ANR.

also required to update entries too which already exists

For that you can specify a conflict resolution strategy, e.g.

INSERT ... ON CONFLICT REPLACE;