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:

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.