How to copy a table to another SQLite database?
1. Using SQL query
First you attach database
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
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
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
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.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download TablePlus for iOS