How to copy data from one SQLite database to another

SQLite provides two different ways to copy data from one database to another database. These include:

• Using the SQL command ATTACH
• Using the SQLite command-line tool .dump and .read.

How to copy data using the ATTACH command

The ATTACH command is used to attach one database to the current database connection.

Let’s say we have two databases portal_db and acct_db and we want to copy data from acct_db to portal_db.

The following steps are involved in copying data:

1. Connect to the account database using command-line

sqlite3 acct_db

2. Use the ATTACH command to add the portal_db to acct_db.

ATTACH DATABASE file_name AS portal_db;

Note: The file_name represents the path to the database.

3. Use the INSERT INTO command to copy data from acct_db to portal_db.

INSERT INTO portal_db.table_name SELECT * FROM acct_db.table_name;

Note: You can also create the table if the table you want to copy to does not exist in the database portal_db.

CREATE TABLE portal_db.table_name(table_definition);

How to copy data using SQLite command-line tool .dump and .read

We’ll be using the same databases: portal_db and acct_db defined in the previous example.

The following steps are involved in copying data:

1. Use the .dump to dump the table table_name from acct_db database.

c:\sqlite>sqlite3.exe acct_db.sqlite
sqlite> .output table_dump.sql
sqlite> .dump table_name
sqlite> .quit

Note: The table_name represents the name of the table.

2. Use the .read to read the dump into the portal_db database.

c:\sqlite>sqlite3.exe portal_db.sqlite sqlite>
sqlite> .read table_dump.sql