[{"_1":2,"_32":-5,"_33":-5},"loaderData",{"_3":4},"routes/_main.tags.$tag",{"_5":6,"_31":19},"posts",[7],{"_8":9,"_10":11,"_12":13,"_14":15,"_16":17,"_25":26,"_27":28,"_29":30},"slug","sqlite-insert-headers-trick","title","How to Transfer SQLite Data Between Databases with Column-Safe Exports","description","Discover how combining SQLite's .mode insert with .headers on creates more readable and portable INSERT statements for data export and migration.","date","2025-08-28","tags",[18,19,20,21,22,23,24],"command-line","data-export","database","migration","sql","sqlite","tips","content","

How to Transfer SQLite Data Between Databases with Column-Safe Exports

While most developers are familiar with SQLite's .dump command for full database exports or .schema for structure-only exports, there's a lesser-known but incredibly useful command: .mode insert. This command generates INSERT statements from query results, making it perfect for partial data exports or exporting data in a specific order.

But there's an even better trick that makes the output much more readable and portable: combining it with .headers on.

The Basic Approach

You can export data as INSERT statements using .mode insert:

sqlite> .mode insert mytable\nsqlite> SELECT * FROM users;\nINSERT INTO mytable VALUES(1,'John','john@example.com');\nINSERT INTO mytable VALUES(2,'Jane','jane@example.com');\n

This works, but the output relies on column order, making it fragile when importing to databases with different column arrangements.

The Magic Trick

Add .headers on before your query:

sqlite> .mode insert mytable\nsqlite> .headers on\nsqlite> SELECT 1 id, 'John' name, 'john@example.com' email;\nINSERT INTO mytable(id,name,email) VALUES(1,'John','john@example.com');\n

Real-World Example

Here's how to export a complete table with proper column names:

sqlite3 mydb.sqlite ".mode insert users" ".headers on" ".output export.sql" "SELECT * FROM users;"\n

This generates:

INSERT INTO users(id,name,email,created_at) VALUES(1,'John','john@example.com','2023-01-15');\nINSERT INTO users(id,name,email,created_at) VALUES(2,'Jane','jane@example.com','2023-01-16');\n

Bonus: Skip Conflicts

For cases where you only want to add new records without overwriting existing ones, you can easily replace INSERT with INSERT OR IGNORE:

sed 's/INSERT INTO/INSERT OR IGNORE INTO/g' export.sql\n

Why This Matters

Portable: Works regardless of column order in target database
Readable: Column names make the SQL self-documenting
Safer: Explicit column mapping prevents import errors
Debuggable: Easy to spot data issues when columns are named

This simple combination of two SQLite dot commands turns basic data dumps into production-ready SQL scripts.

Reference

For more information about SQLite's command-line interface and dot commands, see the official SQLite CLI documentation.

","excerpt","How to Transfer SQLite Data Between Databases with Column-Safe Exports\nWhile most developers are familiar with SQLite's .dump command for full database exports or .schema for structure-only...","readingTime",2,"tag","actionData","errors"]

Posts tagged "data-export"

How to Transfer SQLite Data Between Databases with Column-Safe Exports