SQLite Dump Command

  Total Views : 365
  Comments : 0
  Keywords : SQLite dump command to dump databases, tables and tables schema with examples, Use dump command in sqlite databases to import with examples

Here we will learn dump command in sqlite and how to use sqlite dump command to dump databases, tables with examples.

SQLite Dump Command

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.

Syntax of SQLite Dump Command

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.

SQLite Dump Command Examples

Following are the examples of using SQLite Dump command to dump tables and databases based on our requirements.

SQLite Dump Database Table

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(avalues(1);

INSERT INTO test_dump(avalues(2);

INSERT INTO test_dump(avalues(3);

Once we execute above queries we will check the data of “test_dump” table using following query. 

 

sqliteselect 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_keysOFF;

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.

Redirect Dump 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. 

 

SQLite Dump Database Table to Output File in Specified Folder

 

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;

SQLite Dump Whole Database

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;

SQLite Dump Only Table Schema

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.

 

SQLite Dump Database Tables Schema to Output File in Specified Folder

 

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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User