135

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
webminal.org
  • 44,948
  • 37
  • 94
  • 125
  • 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 – webminal.org Jan 12 '10 at 13:22

4 Answers4

222

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);
Dominic Rodger
  • 97,747
  • 36
  • 197
  • 212
  • 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 – webminal.org Jan 12 '10 at 13:28
  • 1
    thanks It's working now. I missed out ; and included invalid chars like "-". Now it's fine.Thanks !!! – webminal.org Jan 13 '10 at 05:19
  • @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. – Dominic Rodger Jan 13 '10 at 06:51
  • For postgresql you can use pg2sqlite for that, see my [answer](https://stackoverflow.com/a/69293251/1213348) – caiiiycuk Sep 23 '21 at 02:21
  • Does the .dump command produce an sql file that will drop all tables etc if I do `.read `? – chovy Sep 15 '22 at 13:16
92

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Eifion
  • 5,403
  • 2
  • 27
  • 27
  • Me too (Windows command line syntax). Thanks. Sure is slow, though. – Barton Apr 12 '13 at 20:05
  • 4
    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. – Barton Apr 12 '13 at 20:56
32

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
eureka
  • 509
  • 1
  • 5
  • 9
  • this worked for me, although i ran sqlite3 as a command in the terminal so: `sqlite3 DB.db ".read db.sql"` – Bugbeeb Mar 03 '20 at 01:18
28

You can also do: 

sqlite3 database.db -init dump.sql
Ian Newland
  • 1,574
  • 1
  • 18
  • 20