Executing sql scripts using command line tools


SQL Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For SQL Server 2005, when in interactive mode of sqlcmd, use

:r c:\MyFolder\MyScript.sql

to read and execute a script file. You may have to type

go

afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:\MyFolder\MyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

\. c:\MyFolder\MyScript.sql (on Windows)

Note there should be a backward slash right before the dot. In fact, please pay attention to all backward slashes in Windows directory names. Or

source ~/MyFolder/MyScript.sql (on *nix)

to run a script file.
If you want to save a query result from mysql command line to a file, use

SELECT columnName FROM tableName INTO OUTFILE '/tmp/somefile.txt'

mysql -h MyHost -u MyUser -t -p < ~/MyFolder/MyScript.sql The -t is not necessary here, but it presents the results in table/grid format, therefore more readable. You may need to add the -D (or –database) parameter. In Oracle sqlplus, while in interactive mode, use

start c:\MyFolder\MyScript.sql (on Windows)

or

@ ~./MyFolder/MyScript.sql (on *nix)

Note that if the script file name has the .sql extension at the end, it is not necessary to type .sql. sqlplus assumes the .sql extension.

For batch mode, make sure you have exit as the last line of the script file, and run this

sqlplus MyUser@OracleSID @c:\MyFolder\MyScript.sql
, , , ,

3 responses to “Executing sql scripts using command line tools”

  1. Please tell me how to execute an sql script from “Microsoft SQL Server Management Studio” in SQL Server 2005

  2. Please tell me how to execute an sql script from “Microsoft SQL Server Management Studio” in SQL Server 2005. please inform me quickly.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.