Multiple database operations from sql query

Question

I have two instances of SQLiteDatabase database. And I need to copy data from one to another. I need to execute this query:

INSERT INTO `toDB`.`tableName` SELECT * FROM `fromDB`.`tableName`

so, How can I do this my database instances? How to replace toDB and fromDB ?

do you need to copy from one table to another with in the same database? — waqaslam
– waqaslam, Commented Apr 29, 2012 at 11:16

zapl · Accepted Answer · 2012-04-29 11:27:08Z

2

Never tried that but it should work this way:

you have to ATTACH the other database at SQLite level so sqlite can access it directly.

For example you open the database that shall be the toDB and you issue the following command (via execSQL)

ATTACH DATABASE '/data/data/your.package/databases/dbname.db' AS fromDB

you should have access to the other database now and can do

INSERT INTO main.tableName SELECT * FROM fromDbB.tableName

the database you opened originally has the name "main" automatically.

You can and should get the path to your databases via Context#getDatabasePath since there is no guarantee that this path is the same on all devices.

edited Apr 29, 2012 at 11:27

answered Apr 29, 2012 at 11:15

zapl

64.1k10 gold badges126 silver badges160 bronze badges

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

5 Comments

waqaslam Over a year ago

but can you access database of other apps with /data/data/...?

zapl Over a year ago

If the database file has the right permissions yes: MODE_WORLD_READABLE but usually no.

pleerock Over a year ago

Fortunately my database storages in sdcard, so this method has worked. Thanks. But I still want to know if there is more optimal method

zapl Over a year ago

you can copy the database file to internal storage and use it from there instead of importing it. But for copying data from db to db that should be the fastest you can do.

pleerock Over a year ago

in my case I dont need it. I need to import that table, change table structure and put into my application's database. But thanks!

mianlaoshu · Accepted Answer · 2014-09-26 01:31:12Z

Yes, you can do this as following:

    DatabaseHelper dbHelper = DatabaseHelper.create(CMOSApplication.getInstance());
    SQLiteDatabase db = null;
    try {
        db = dbHelper.getWritableDatabase();
        db.execSQL("attach database ? as oldDB",
                new String[] { CMOSApplication.getInstance().getDatabasePath("cmos_database").getPath() });
        db.execSQL("insert into task select * from oldDB.task");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        if(db != null){
            try {
                db.execSQL("detach oldDB");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

Collectives™ on Stack Overflow

Multiple database operations from sql query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related