Copying data from one SQLite database to another

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> .databasesyou 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.