Here we will learn dump command in sqlite and how to use sqlite dump command to dump databases, tables with examples.
In SQLite Dump command is used to dump the databases, table’s schema and tables data based on our requirements.
By using SQLite Dump command we can save the structure and data of the database tables in SQL format to the disk.
Following is the syntax of SQLite Dump command to dump databases, tables structure and data based on our requirements.
sqlite> .dump
The above syntax will dump complete database.
Now we will see how to use SQLite dump command to dump database and tables with examples.
Following are the examples of using SQLite Dump command to dump tables and databases based on our requirements.
Now we will see how to dump particular table in database for that design table called “test_dump” in your database and insert some data using following query.
CREATE TABLE test_dump (a INTEGER);
INSERT INTO test_dump(a) values(1);
INSERT INTO test_dump(a) values(2);
INSERT INTO test_dump(a) values(3);
Once we execute above queries we will check the data of “test_dump” table using following query.
sqlite> select * from test_dump
a
---------
1
2
3
Now we will see how to dump “test_dump” table from database using following command.
sqlite> .dump test_dump
When we execute above command we will get the output like as shown following.
PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
CREATE TABLE test_dump(a INTEGER);
INSERT INTO 'test_dump' VALUES(1);
INSERT INTO 'test_dump' VALUES(2);
INSERT INTO 'test_dump' VALUES(3);
COMMIT;
If you observe here we just dump smaller table but if we want to dump larger database, then SQLite output will take longer time to generate output on screen. The only solution is we can redirect output to file.
If we want to dump our database to SQL file then first we need to use “.output” command. After that we need to use “.dump” command to redirect result to defined file.
Following is the example of illustrating above process to dump database or tables to output file.
.output test_dump.sql
.dump test_dump
.quit
The above statements will create “test_dump.sql” in the location where sqlite3.exe is exist like as shown below.
If you open “test_dump.sql” file that will contain content like shown following.
PRAGMA foreign_keys =OFF;
BEGIN TRANSACTION;
CREATE TABLE test_dump(a INTEGER);
INSERT INTO 'test_dump' VALUES(1);
INSERT INTO 'test_dump' VALUES(2);
INSERT INTO 'test_dump' VALUES(3);
COMMIT;
Suppose if we want to dump whole database instead of just one table, then we need to use dump command without specifying any table name as shown following.
sqlite> .dump
The above command will give output like as shown following.
PRAGMA foreign_keys =OFF;
BEGIN TRANSACTION;
CREATE TABLE test_dump(a INTEGER);
INSERT INTO 'test_dump' VALUES(1);
INSERT INTO 'test_dump' VALUES(2);
INSERT INTO 'test_dump' VALUES(3);
COMMIT;
To dump only schema of specific table, SQLite offers “.schema” command and we can redirect output of this command to external file using “.output” command.
Following is the example of dumping only schema of “products” table.
sqlite> .outputschema.sql
sqlite> .schema products
sqlite> .quit
Whe we execute above queries “schema.sql” is generated at the location where our sqlite3.exe is exist like as shown following.
The content of “schema.sql” file will be like as shown following.
CREATE TABLE products
(
Product_id INTEGER PRIMARY KEY,
Product_name VARCHAR NOT NULL,
Qty INTEGER
);
This is how we can use “dump” command in SQLite to dump databases and tables based on our requirements.