[database] Copying data from one SQLite database to another

I have 2 SQLite databases with common data but with different purposes and I wanted to avoid reinserting data, so I was wondering if it was possible to copy a whole table from one database to another?

If you use DB Browser for SQLite, you can copy the table from one db to another in following steps:

0. Open two instances of the app and load the source db and target db side by side.
1. If the target db does not have the table, "Copy Create Statement" from the source db and then paste the sql statement in "Execute SQL" tab and run the sql to create the table.
2. In the source db, export the table as a CSV file.
3. In the target db, import the CSV file to the table with the same table name. The app will ask you do you want to import the data to the existing table, click yes. Done.

For one time action, you can use .dump and .read.

Dump the table my_table from old_db.sqlite

c:\sqlite>sqlite3.exe old_db.sqlite
sqlite> .output mytable_dump.sql
sqlite> .dump my_table
sqlite> .quit

Read the dump into the new_db.sqlite assuming the table there does not exist

c:\sqlite>sqlite3.exe new_db.sqlite
sqlite> .read mytable_dump.sql

Now you have cloned your table. To do this for whole database, simply leave out the table name in the .dump command.

Bonus: The databases can have different encodings.

I needed to move data from a sql server compact database to sqlite, so using sql server 2008 you can right click on the table and select 'Script Table To' and then 'Data to Inserts'. Copy the insert statements remove the 'GO' statements and it executed successfully when applied to the sqlite database using the 'DB Browser for Sqlite' app.

Consider a example where I have two databases namely allmsa.db and atlanta.db. Say the database allmsa.db has tables for all msas in US and database atlanta.db is empty.

Our target is to copy the table atlanta from allmsa.db to atlanta.db.

Steps

1. sqlite3 atlanta.db(to go into atlanta database)
2. Attach allmsa.db. This can be done using the command ATTACH '/mnt/fastaccessDS/core/csv/allmsa.db' AS AM; note that we give the entire path of the database to be attached.
3. check the database list using sqlite> .databases you can see the output as

seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /mnt/fastaccessDS/core/csv/atlanta.db                  
2    AM               /mnt/fastaccessDS/core/csv/allmsa.db 

4. now you come to your actual target. Use the command INSERT INTO atlanta SELECT * FROM AM.atlanta;

This should serve your purpose.

Easiest and correct way on a single line:

sqlite3 old.db ".dump mytable" | sqlite3 new.db

The primary key and the columns types will be kept.

Objective-C code for copy Table from a Database to another Database

-(void) createCopyDatabase{

          NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
          NSString *documentsDir = [paths objectAtIndex:0];

          NSString *maindbPath = [documentsDir stringByAppendingPathComponent:@"User.sqlite"];;

          NSString *newdbPath = [documentsDir stringByAppendingPathComponent:@"User_copy.sqlite"];
          NSFileManager *fileManager = [NSFileManager defaultManager];
          char *error;

         if ([fileManager fileExistsAtPath:newdbPath]) {
             [fileManager removeItemAtPath:newdbPath error:nil];
         }
         sqlite3 *database;
         //open database
        if (sqlite3_open([newdbPath UTF8String], &database)!=SQLITE_OK) {
            NSLog(@"Error to open database");
        }

        NSString *attachQuery = [NSString stringWithFormat:@"ATTACH DATABASE \"%@\" AS aDB",maindbPath];

       sqlite3_exec(database, [attachQuery UTF8String], NULL, NULL, &error);
       if (error) {
           NSLog(@"Error to Attach = %s",error);
       }

       //Query for copy Table
       NSString *sqlString = @"CREATE TABLE Info AS SELECT * FROM aDB.Info";
       sqlite3_exec(database, [sqlString UTF8String], NULL, NULL, &error);
        if (error) {
            NSLog(@"Error to copy database = %s",error);
        }

        //Query for copy Table with Where Clause

        sqlString = @"CREATE TABLE comments AS SELECT * FROM aDB.comments Where user_name = 'XYZ'";
        sqlite3_exec(database, [sqlString UTF8String], NULL, NULL, &error);
        if (error) {
            NSLog(@"Error to copy database = %s",error);
        }
 }

First scenario: DB1.sqlite and DB2.sqlite have the same table(t1), but DB1 is more "up to date" than DB2. If it's small, drop the table from DB2 and recreate it with the data:

> DROP TABLE IF EXISTS db2.t1; CREATE TABLE db2.t1 AS SELECT * FROM db1.t1;

Second scenario: If it's a large table, you may be better off with an INSERT if not exists type solution. If you have a Unique Key column it's more straight forward, otherwise you'd need to use a combination of fields (maybe every field) and at some point it's still faster to just drop and re-create the table; it's always more straight forward (less thinking required).

THE SETUP: open SQLite without a DB which creates a temporary in memory main database, then attach DB1.sqlite and DB2.sqlite

> sqlite3
sqlite> ATTACH "DB1.sqlite" AS db1
sqlite> ATTACH "DB2.sqlite" AS db2

and use .databases to see the attached databases and their files.

sqlite> .databases
main: 
db1: /db/DB1.sqlite
db2: /db/DB2.sqlite