Copying data from one SQLite database to another

Question

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;

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.

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.

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.