Exporting data from SQLite3
database, database-table, export, sql, sqlite
Solution
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).
Problem
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?