SQLite - How to copy data from one database to another?

How to copy a table to another SQLite database?

1. Using SQL query

First you attach database new_db with old_db to associate them in the current database connection.

Assume that you are connected to the old_db, now run the ATTACH command to attach the new_db:

ATTACH DATABASE file_name AS new_db;

If the table you want to copy data to does not exist in the new_db yet, create it:

CREATE TABLE new_db.table_name(table_definition);

Then insert from old_db to new_db:

INSERT INTO new_db.table_name SELECT * FROM old_db.table_name;

If the columns are not matched up in order:

INSERT INTO new_db.table_name(col1, col2) SELECT col1, col2 FROM old_db.table_name;

2. Using SQLite command-line tool

Open the old database then dump the table

sqlite> .output table_dump.sql
sqlite> .dump table_name
sqlite> .quit

Then open the new database and read the dump:

sqlite> .read table_dump.sql

---

Need a good GUI tool for SQLite? TablePlus is a native GUI client for multiple relational databases, including SQLite. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS