CODEWITHSUNDEEP
sqldatabasesqlitedatabase-dump
carl
carl

Reputation: 4436

creating sqlite3 dump files with all table values

I can generate a sqlite3 dump file with 

sqlite3 app.db .dump > app_dump.sql

however, if there is a row in that database which has no entry it is just left out in the dump file. This is a problem when using the dump file to read the data into another database. Is there a way to force the dump file to have all values defined in the table? So if there is no entry it just uses empty string '' or something like that? thanks carl

Upvotes: 0

Views: 1824

Answers (2)

social
social

Reputation: 537

yes or from the sqlite3 cli:

sqlite> .output app_dump.sql
sqlite> .dump <OPTIONAL table_name>

sqlite> .read app_dump.sql

File will be in your current path else locate it e.g C:/sql/app_dump.sql or ./app_dump.sql

If it rearranges your columns e.g .header on does not match data then:

ALTER TABLE table_name
RENAME COLUMN current_name TO new_name;

Upvotes: 0

CL.
CL.

Reputation: 180270

The output of .dump always includes all table rows:

sqlite> create table t(x);
sqlite> insert into t values (null), (0), (''), (x'');
sqlite> select * from t;

0


sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(x);
INSERT INTO "t" VALUES(NULL);
INSERT INTO "t" VALUES(0);
INSERT INTO "t" VALUES('');
INSERT INTO "t" VALUES(X'');
COMMIT;

Upvotes: 1

Related Questions