Copying data from one SQLite database to another

Question

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?

Answer 1

You'll have to attach Database X with Database Y using the ATTACH command, then run the appropriate Insert Into commands for the tables you want to transfer.

INSERT INTO X.TABLE SELECT * FROM Y.TABLE;

Or, if the columns are not matched up in order:

INSERT INTO X.TABLE(fieldname1, fieldname2) SELECT fieldname1, fieldname2 FROM Y.TABLE;

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.