Exporting data from SQLite 3

Question

I need a simple way to export data from an SQLite database of multiple tables, then import them into another database.

Here is my scenario. I have 5 tables: A, B, C, D, E.

Each table has a primary key as the first column called ID. I want a Unix command that will dump ONLY the data in the row from the primary key in a format that can be imported into another database.

I know I can do a

sqlite3 db .dump | grep INSERT

but that gives me ALL data in the table. I'm not a database expert, and I'm trying to do this with all unix commands in which I can write a shell script, rather than writing C++ code to do it (because that's what people are telling me that's the easiest way). I just refuse to write C++ code to accomplish a task that possible can be done in 4-5 statements from the command line.

Any suggestions?

Answer 1

This may look a little weird, however you may give it a try:

Create text file and place following statements there:

.mode insert
.output insert.sql
select * from TABLE where STATEMENT; -- place the needed select query here
.output stdout

Feed this file to sqlite3:

$ sqlite3 -init initf DATA.DB .schema > schema.sql

As the result you will get two files: one with simple "inserts" (insert.sql) and another with db schema (schema.sql).

Answer 2

Suggest finding a tool that can take your query and export to a CSV. It sounds like you wanted a script. Did you want to reuse and automate this?

For other scenarios, perhaps consider the sqlite-manager Firefox plugin. It supports running your adhoc queries, and exporting the results to a CSV.

Within that, give it this statement:

 SELECT ID FROM TableA

Repeat for each table as you need.