How to import .sql files into SQLite3?

Question

I have .sql files which have the following content:

#cat db.sql
create table server(name varchar(50),ipaddress varchar(15),id init)
create table client(name varchar(50),ipaddress varchar(15),id init)

How do I import this file into SQLite so that these are created automatically?

Catfish

sqlite3 DB.db < db.sql Error: incomplete SQL: create table server(name varchar(50),ipaddress varchar(15),id init) create table client(name varchar(50),ipaddress varchar(15),id init) what's this error mean? I tried both methods >.read db.sql and sqlite3 DB.db < db.sql – Catfish

Answer 1

From a sqlite prompt:

sqlite> .read db.sql

Or:

cat db.sql | sqlite3 database.db

Also, your SQL is invalid - you need ; on the end of your statements:

create table server(name varchar(50),ipaddress varchar(15),id init);
create table client(name varchar(50),ipaddress varchar(15),id init);

Indene

sqlite3 DB.db < db.sql Error: incomplete SQL: create table server(name varchar(50),ipaddress varchar(15),id init) create table client(name varchar(50),ipaddress varchar(15),id init) what's this error mean? I tried both methods >.read db.sql and sqlite3 DB.db < db.sql...Thanks – Catfish

thanks It's working now. I missed out ; and included invalid chars like "-". Now it's fine.Thanks !!! – Catfish

@lakshmipathi, if it's working you can mark one of the two answers that answered your question as accepted by clicking the tick under the vote count next to the answer. – Indene

For postgresql you can use pg2sqlite for that, see my answer – Tobacco

Does the .dump command produce an sql file that will drop all tables etc if I do .read <file>? – Bedfellow

Answer 2

Use sqlite3 database.sqlite3 < db.sql. You'll need to make sure that your files contain valid SQL for SQLite.

Shiny

Me too (Windows command line syntax). Thanks. Sure is slow, though. – Nurserymaid

I got impatient building from a 40+Mb .sql file, terminated sqlite3 database.sqlite3 < db.sql in favor of sqlite> .read db.sql. The alternative is just as slow, it turns out. – Nurserymaid

Answer 3

Alternatively, you can do this from a Windows commandline prompt/batch file:

sqlite3.exe DB.db ".read db.sql"

Where DB.db is the database file, and db.sql is the SQL file to run/import.

Twobyfour

this worked for me, although i ran sqlite3 as a command in the terminal so: sqlite3 DB.db ".read db.sql" – Cassiodorus

Use .\sqlite.exe to load from the current directory when using Powershell on Windows 11 – Dielu

Answer 4

You can also do:

sqlite3 database.db -init dump.sql

Astounding

This will leave the sqlite3 client running in the interactive prompt. – Wandie

Answer 5

For example, you export apple.db to backup.sql with .dump or .schema as shown below. *backup.sql is created if it doesn't exist and .dump can export schema with data and .schema can export only schema and my answer explains how to export a database:

sqlite3 apple.db .dump > backup.sql

Or:

sqlite3 apple.db .schema > backup.sql

Now, you can import backup.sql into orange.db as shown below. *orange.db is created if it doesn't exist:

sqlite3 orange.db < backup.sql

Or:

sqlite3 orange.db
...
sqlite> .read backup.sql

Be careful, if you directly import apple.db into orange.db as shown below:

sqlite3 orange.db < apple.db

Or:

sqlite3 orange.db
...
sqlite> .read apple.db

Then, you get the error below:

Parse error near line 1: near "SQLite": syntax error

Orelu