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?
asked Jul 25, 2012 at 6:03
Add a comment
|
6 Answers
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.
answered Jul 25, 2012 at 6:11
-
2Unfortunately, not all shells understand
<as input redirection. (E.g., PowerShell.)– AlanCommented Jun 3, 2016 at 17:44 -
@Alan PowerShell has some input redirection mechanism, no? And if not there's always bitops's approach. Commented Jun 3, 2016 at 17:52
-
@muistooshort see my comment to bitops. The sqlite dot commands work in the sql statement parameter so you can .read the file you want without resorting to input redirection. Commented Jul 6, 2018 at 8:29
-
2@ChrisBecke they are different solutions.
<will exit the SQLite prompt immediately and return the error code to the shell..read file.sqlwill leave the prompt up and-init file.sqlwill always return 0, so<is the best for scripting. Also it's cross-platform unlike.readwhich doesn't support Windows paths. Commented Aug 14, 2020 at 13:45
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.
-
22For use in scritps, you can run the
.readcommand directly from thesqlite3command:sqlite3 autction.db '.read create.sql'. Commented May 13, 2020 at 16:57 -
1See Command Line Shell For SQLite: 7.2. Reading SQL from a file. Commented Nov 7, 2021 at 13:34
-
For Windows CMD line it is important to use doublequotes for the
".read script.sql"part. With single quotes you get anError: in prepare, unrecognized token: "'.read"– PeterCoCommented May 13, 2022 at 7:36
In order to execute simple queries and return to my shell script, I think this works well:
$ sqlite3 example.db 'SELECT * FROM some_table;'
-
3
-
17@ColonelThirtyTwo Yes, this is very close to mu is too short's answer. The reason I added an additional answer was to demonstrate a method to quickly execute an inline command, rather than taking the additional step of creating an SQL file to store the command in.– remeikaCommented Aug 24, 2015 at 23:02
-
6@remeika I think the more idiomatic way to execute an inline command would be
sqlite3 example.db 'SELECT * FROM some_table;', rather than piping an echo Commented Jan 21, 2018 at 20:49 -
1Also, (after the edit), it demonstrates that
sqlite3reads commands to execute not only fromstdin, but also from the last argument. Commented Nov 11, 2019 at 22:54
For those using PowerShell
PS C:\> Get-Content create.sql -Raw | sqlite3 auction.db
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
answered Nov 7, 2021 at 13:40
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)
