How do I dump the data of some SQLite3 tables?

Question

How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)? The dump should be in SQL format, as it should be easily re-entered into the database latter and should be done from the command line. Something like

sqlite3 db .dump

but without dumping the schema and selecting which tables to dump.

Answer 1

You could do a select on the tables inserting commas after each field to produce a csv, or use a GUI tool to return all the data and save it to a csv.

Answer 2

You could use a tool like SQLite Administrator.

There's a long list of such tools here.

Answer 3

The best method would be to take the code the sqlite3 db dump would do, excluding schema parts.

Example pseudo code:

SELECT 'INSERT INTO ' || tableName || ' VALUES( ' || 
  {for each value} ' quote(' || value || ')'     (+ commas until final)
|| ')' FROM 'tableName' ORDER BY rowid DESC

See: src/shell.c:838 (for sqlite-3.5.9) for actual code

You might even just take that shell and comment out the schema parts and use that.

Answer 4

You don't say what you wish to do with the dumped file.

I would use the following to get a CSV file, which I can import into almost everything

.mode csv
.header on
.out file.dmp
select * from emp;

If you want to reinsert into a different SQLite database then:

.mode insert
.out file.sql
select * from emp

Answer 5

Not the best way, but at lease does not need external tools (except grep, which is standard on *nix boxes anyway)

sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"'

but you do need to do this command for each table you are looking for though.

Answer 6

You can specify one or more table arguments to the .dump special command, e.g.sqlite3 db '.dump "table1" "table2"'.