How to dump the data of some SQLite3 tables?

Tailoring the data dump

Exporting to CSV

For CSV output, beneficial for data analysis or importing into non-SQL applications, switch the mode and output to a CSV file:

.mode csv
.headers on 
.out my_data.csv 
SELECT * FROM my_table; --Because every record counts


This creates my_data.csv, supplying informative headers for easier use later. Ideal when SQL insert statements aren't paramount.

Dumping data as SQL Insert

When your goal is moving data between two SQLite, loves .mode insert:

.mode insert
.out data_inserts.sql
SELECT * FROM my_table; --Grabbing all the goodies in one go


This results in data_inserts.sql, which contains ready-to-use insert statements, great for direct data transfer.

Before you Dump

Before any dump, cross-check your target database. If it has an identical structure, .mode insert will be perfect. In case of any differences, make sure to align the insert commands accordingly or switch to a universally accepted format, CSV. Ensure the table's name in the target database is correctly referred during the dump.

Extra data dumping techniques

Large datasets

If you're dealing with large tables, be mindful of resources. Implement LIMIT and OFFSET to dump in chunks and minimize memory strain.

Verifying output integrity

Run a .integrity_check before dumping, ensuring no corruption:

.integrity_check --Because we all like clean, pure data


Handle any issues before exporting lest they multiply during data restoration.

Schema conflicts

If you need data only for reinsertion and the schema is already present in the target database:

sqlite3 my_database.db ".dump 'table_name'" | grep -v 'CREATE TABLE' > data_only_dump.sql


This uses grep to omit the CREATE TABLE statements, allowing smoother insertion sans schema complications.