Execute SQLite script
SqliteSqlite Problem Overview
I start up sqlite3 version 3.7.7, unix 11.4.2 using this command:
sqlite3 auction.db
where auction.db has not already been created.
sqlite> auction.db < create.sql;
gives me this error: near "auction": syntax error
How can I run the script?
Sqlite Solutions
Solution 1 - Sqlite
You want to feed the create.sql into sqlite3 from the shell, not from inside SQLite itself:
$ sqlite3 auction.db < create.sql
SQLite's version of SQL doesn't understand < for files, your shell does.
Solution 2 - Sqlite
There are many ways to do this, one way is:
sqlite3 auction.db
Followed by:
sqlite> .read create.sql
In general, the SQLite project has really fantastic documentation! I know we often reach for Google before the docs, but in SQLite's case, the docs really are technical writing at its best. It's clean, clear, and concise.
Solution 3 - Sqlite
In order to execute simple queries and return to my shell script, I think this works well:
$ sqlite3 example.db 'SELECT * FROM some_table;'
Solution 4 - Sqlite
For those using PowerShell
PS C:\> Get-Content create.sql -Raw | sqlite3 auction.db
Solution 5 - Sqlite
If you are using the windows CMD you can use this command to create a database using sqlite3
C:\sqlite3.exe DBNAME.db ".read DBSCRIPT.sql"
If you haven't a database with that name sqlite3 will create one, and if you already have one, it will run it anyways but with the "TABLENAME already exists" error, I think you can also use this command to change an already existing database (but im not sure)
Solution 6 - Sqlite
sqlite3 -init create.sql auction.db .quit
When I enter the command man sqlite3, I see under "OPTIONS":
> -init file
> Read and execute commands from file , which can contain a mix of SQL statements and meta-commands.
and under "SQLITE META-COMMANDS":
> .quit Exit this program
See also: Command Line Shell For SQLite: 3. Special commands to sqlite3 (dot-commands).
Also, when I enter the command sqlite3 -help, I see under "OPTIONS":
> -init FILENAME read/process named file