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.

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

TablePlus GUI Tool SQLite