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.

For batch mode, use

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.

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 Comments »

  1. Alfaz Said,

    August 6, 2008 @

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

  2. Alfaz Said,

    August 6, 2008 @

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

  3. Nhut Nguyen Said,

    September 17, 2011 @

    How to convert SQL in Microsoft SQL Server 2005

RSS feed for comments on this post · TrackBack URI

Leave a Comment