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

User · Answer

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

sqlite3 atlanta.db(to go into atlanta database)
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.
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

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

This should serve your purpose.

User · Answer

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    gt  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   gt  sqlite3 sqlite gt  ATTACH  DB1 sqlite  AS db1 sqlite gt  ATTACH  DB2 sqlite  AS db2   and use  databases to see the attached databases and their files   sqlite gt   databases main   db1   db DB1 sqlite db2   db DB2 sqlite

User · Answer

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

User · Answer

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

User · Answer

For one time action  you can use  dump and  read   Dump the table my table from old db sqlite  c  sqlite gt sqlite3 exe old db sqlite sqlite gt   output mytable dump sql sqlite gt   dump my table sqlite gt   quit   Read the dump into the new db sqlite assuming the table there does not exist  c  sqlite gt sqlite3 exe new db sqlite sqlite gt   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

User · Answer

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    amp database   SQLITE OK                NSLog   Error to open database                       NSString  attachQuery    NSString stringWithFormat   ATTACH DATABASE        AS aDB  maindbPath           sqlite3 exec database   attachQuery UTF8String   NULL  NULL   amp 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   amp 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   amp error           if  error                NSLog   Error to copy database    s  error

User · Answer

If you use DB Browser for SQLite  you can copy the table from one db to another in following steps   Open two instances of the app and load the source db and target db side by side  If the target db does not have the table   quot Copy Create Statement quot  from the source db and then paste the sql statement in  quot Execute SQL quot  tab and run the sql to create the table  In the source db  export the table as a CSV file  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

User · Answer

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

[database] Copying data from one SQLite database to another

Steps

Examples related to database

Examples related to sqlite