SQLite | SQLite Commands | Rebuilding a Database from a Dump (.read Command)
The .dump command saves a database’s schema and data as SQL statements. The .read command can execute that dump file to create a database with the same contents. This article explains how to import a dump file with .read.
See Dumping a Database (.dump Command) for details about creating a dump.
Importing a Dump File
Use .read to import a dump into a new database.
.read FILENAME
Specify the file to import. It must contain SQL statements, such as those produced by .dump. SQLite executes the statements in order when it loads the file.
The following dump.txt file contains a dump of an existing database.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (name text, old integer, address text);
INSERT INTO user VALUES('devkuma',28,'Seoul');
INSERT INTO user VALUES('kimkc',22,'Busan');
INSERT INTO user VALUES('araikuma',32,'Seoul');
INSERT INTO user VALUES('happykuma',23,'Seoul');
INSERT INTO user VALUES('mykuma',23,'Daejeon');
CREATE TABLE color (id integer, name text);
INSERT INTO color VALUES(1,'Red');
INSERT INTO color VALUES(2,'Blue');
INSERT INTO color VALUES(3,'White');
CREATE INDEX nameindex on user (name);
COMMIT;
Create a new, empty database named newdb.sqlite3.
sqlite3 newdb.sqlite3
$ sqlite3 newdb.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>
Import the file with .read.
.read ./dump.txt
sqlite> .read ./dump.txt
sqlite>
SQLite executes the statements in the file in order. In this example, it creates two tables and inserts data into both.
Use .tables to confirm that both tables were created.
.tables
sqlite> .tables
color user
sqlite>
Query each table to confirm that the imported data was stored.
select * from user;
sqlite> select * from user;
devkuma|28|Seoul
kimkc|22|Busan
araikuma|32|Seoul
happykuma|23|Seoul
mykuma|23|Daejeon
sqlite>
select * from color;
sqlite> select * from color;
1|Red
2|Blue
3|White
sqlite>
Importing a database dump into a new database rebuilds its schema and data.