SQL stands for Structured Query Language, which is the common language (with minor changes) used to manipulate relational databases. It is used to create, store, retrieve, and manipulate databases and tables. SQLite is a lightweight version of SQL with some major changes, it doesn't have a separate server, it is not a common language, and it can't connect with databases like Oracle or MySQL server.
In this article, we will learn about DUMP Command in SQLite in depth along with its examples, practical implementations, and so on.
SQLite DUMP
The DUMP command in SQLite is used to Backup or Restore any Database, Table, or Schema. But unlike other Functions of SQLite, the DUMP command is used with e "." (Dot) in front of it. These types of SQLite commands are called Dot-Commands. DUMP commands are used to dump Tables, Databases, Schemas, etc to some other file.
How To Use The SQLite Dump Command
In this section, we will learn about the syntax and the working procedure of the DUMP dot command.
As mentioned earlier, the DUMP command is a Dot-Command used in SQLite3, so it is used with a preceding dot.
Syntax:
.dump <table_name> [OPTIONAL]
Explanation:
The <table_name> after the DUMP command must be provided whenever the user want to just dump a single Table and not the entire Database. In other times, it is not necessary to provide anything after the DUMP command.
If we use the .dump command singularly, then all the SQL statements used will be given as output in the same Command Line, it will not be saved anywhere.
To understand the DUMP command in more depth we need a 2 table on which we will perform queries. Here we have table called Employees and Students.
After inserting data into the table, Our table looks:
Employees Table:
Students Table:
Now, if we use the dump command now, without mentioning anything else, then all the SQL statements used to create and populate the table will be given as output.
.dump
Output:
Explanation: Using the DUMP dot command, it alone returns all the commands used till now to create and populate the table. It returns the output as a transaction, that's why the BEGIN TRANSACTION and COMMIT is being displayed here which is not required while writing the actual commands.
Dump Entire Database Into file using DUMP Command
Here, we will dump an entire database into a file using the DUMP command. To do that firstly, we need to provide the name and extension of the file in which we want to store the result with the .output command. Then we have to use the .dump command.
Query:
.output <Location of File with Filename.Extension>
.dump
Now, we will store all those commands i.e the output of the .dump command to a text file named Employee_Details.txt. Write the below commands one by one, just replace the file's location with the user's one:
Query:
.output C:\Users\user\Downloads\SQLite\Database_Details.txt
.dump
Output:
Explanation: Here we are providing the entire path and the name of the file in which we want to store the results of the dump dot command. Even though the file and the SQLite resides in the same folder, it is recommended to provide the entire path of the file to stay on the safer side. After that we will just run the DUMP command and everything will be stored in the file we mentioned in the previous step alongwith the .output command.
Content saved in the Output File:
Dump Specific Table Using Dump Command
In this section we will see how we can use the dump command to dump a specific table of the database into a text file, not the entire database.
Sometimes, we might need to dump only a specific table into another file, not the entire database. For that, the syntax will change a bit. We need to provide the output filename as last time, but with the dump command, we need to provide the name of the specific table, which we want to dump.
Syntax:
.output <Location of File with Filename.Extension>
.dump <Table_Name>
Query:
Here, we will dump just the Employees table into a file named Employee_Table.txt
.output C:\Users\user\Downloads\SQLite\Employee_Table.txt
.dump Employees
Explanation: Here, just like the last time we are using the output dot-command and providing the entire path of the file in which we want to store the results / commands. But the difference is, here we just want to store the commands used to create and populate a certain table of the database, not the entire database. This is why we need to provide the name of the Table after the DUMP command to signify that we want to store the commands related to that table only.
Content of the Employee_Table Text File After Dumping:
.JPG)
Explanation: As we can see, only the details of the Employees table has been saved / dumped. Not the entire Database.
Dump Tables Structure Only Using Schema Command
Here we will see how we can only dump the schmea i.e the structure of the table or tables using the dump command. There is a variation of this command, using it alone will store the schema of all the tables present in database, but if we pass the name of a specific table then it will only store that.
Now, we will not dump the contents of any table, but we will dump the structures of the table using the .schema command. .schema is a dot-command same as of the .dump command, it is used to dump only the structure or schema of the tables, just like the .dump command, it is used after the .output command.
Syntax:
.output <Location of File with Filename.Extension>
.schema
Query:
We will store the structure in a file called Table_Structure.txt. Write the below command to Dump the tables structure using the schema command:
.output C:\Users\user\Downloads\SQLite\Table_Structure.txt
.schema
Explanation: Just like everytime, we will first provide the entire path of the file in which we want to store the schema of the tables with the output dot command. Then we will call the schema dot command alone, and this will store every tables' structure into that file.
Contents of the Table_Structure.txt:
Explanation: This stores the structures of all the tables present in the database.
If we want to store the structure of a specific Table of the database, then we need to provide the table name after the .schema command.
Syntax:
.output <Location of File with Filename.Extension>
.schema <Table_Name>
Query:
Saving the Schema of the Students table:
.output C:\Users\user\Downloads\SQLite\Student_Structure.txt
.schema Students
Explanation: Here after providing the path of the file in which we want to store the output, we are using the schema command alongside the table called Students. This is to signify that we want to store the schema of the only Students table, not every table present in database.
Contents of the Student_Structure.txt file:
Dump Data of One or More Tables Into File
Here we will see how we can dump the data of one or more tables into a file using the dump command, whenever we will use the SELECT statement, in the file we mentioned, all the INSERT commands we have used previously will be stored.
To dump the data of one or more tables into a file, we first need to change the mode into insert, this will ensure that whenever we use the select statement in command line, in the file all the insert statements will be dumped. We also need to provide a filename with an extension with the .output dot-command to point out in which file the results will be stored.
Syntax:
.mode insert
.output <filename.extension>
Query:
Now if we run the select statement to fetch the values of Employees table only, all the insert statement used for the Employees table, will be dumped and saved in the file mentioned.
.mode insert
.output mydata.txt
Query:
Now using the Select statement:
SELECT * FROM Employees;
Content of the mydata.txt file:
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(1, 'Sonia', 'Wong',20000,'AL');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(2, 'Neel', 'Lee', 25000, 'FL');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(3, 'Melody','Abott', 23000, 'IA');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(4, 'Trinity', 'Kirk', 21000, 'IL');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(5, 'Miley', 'Webster',28000, 'IN');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(6, 'Sydnee', 'Donaldson', 27000, 'KY');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(7, 'Matilda', 'Roach', 35000, 'MN');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(8, 'Chanel', 'Mcneil', 33000, 'MI');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(9, 'Gilberto', 'Blake', 34000, 'MS');
INSERT INTO "table"(empID, FirstName, LastName, Salary, Location) VALUES(10, 'Harmony', 'Serrano', 32000, 'NV');
If we now run the select statement again but for the Students Table, the content will be updated as below:
Output:
Conclusion
In this article, we saw how we can use the DUMP dot-command of SQLite3 for various purposes. Using it singularly or grouped together with other commands like Schema or Output, the main task what DUMP command does is to store the commands used to create the database, or any table, or the structure of any table.
This command is very much useful when dealing with a large database which consists of a lot of tables, when the user wants to know about the structure of the database, or the commands used to populate the tables, they can just use this DUMP command to get all of them in a file without doing any extra manual work.
Similar Reads
dump command in Linux with examples
The dump command in Linux is used for backing up the filesystem to a storage device. Unlike commands that back up individual files, dump operates on entire filesystems, transferring data to tape, disk, or other storage media for safekeeping. One of its key advantages is the ability to perform increm
6 min read
Basic SQL Commands
Structured Query Language (SQL) is the standard language used for managing and interacting with relational databases. Whether we are retrieving data, updating records, or defining the structure of our data, SQL commands provide a powerful and flexible way to handle these tasks. This article will exp
6 min read
SQL*Plus Command Reference
SQL*Plus is a command-line tool for Oracle Database that allows users to interact with the database using SQL and PL/SQL commands. Here, we will discuss the SQL*Plus commands, and understand how to use the SQL*Plus command-line argument. SQL*Plus CommandsHere is a list of some essential SQLPlus Comm
3 min read
SQL Comments
SQL comments play an essential role in enhancing the readability, maintainability, and documentation of our SQL code. By using comments effectively, developers can provide context, clarify complex logic, and temporarily disable parts of the code. Whether we're working alone or collaborating with a t
4 min read
DROP and TRUNCATE in SQL
The DROP and TRUNCATE commands in SQL are used to remove data from a table, but they work differently. Understanding the difference between these two commands is important for proper database management, especially when dealing with large amounts of data. This article provides an in-depth explanatio
5 min read
PL/SQL Copy Table
Copying tables in PL/SQL is a common task in database management. It involves duplicating the structure and data of an existing table into a new one. This operation can be accomplished using the CREATE TABLE AS SELECT statement, which allows for the creation of a new table based on the result set of
3 min read
Python SQLite - CRUD Operations
In this article, we will go through the CRUD Operation using the SQLite module in Python. CRUD Operations The abbreviation CRUD expands to Create, Read, Update and Delete. These four are fundamental operations in a database. In the sample database, we will create it, and do some operations. Let's di
4 min read
SQLite WHERE Clause
SQLite is the serverless database engine that is used most widely. It is written in c programming language and it belongs to the embedded database family. In this article, you will be learning about the where clause and functionality of the where clause in SQLite. Where ClauseSQLite WHERE Clause is
3 min read
Python SQLite - Insert Data
In this article, we will discuss how can we insert data in a table in the SQLite database from Python using the sqlite3 module. The SQL INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using the INSERT INTO statement for inserting rows: Only values: The firs
3 min read
SQL - DROP View
SQL Views provide a powerful way to simplify complex queries and present data in a more understandable format. However, there may be times when we need to remove a view from our database schema. In SQL, deleting a view is straightforward using the DROP VIEW command. In this article, we will explain
5 min read