Running SQL Scripts from Command Line

Now we are going to learn how to run SQL Queries from Command Line
The SQLCMD utility lets you enter queries, procedures, and script files at the command prompt.
TO simple run a query and get the ouptput in command line, use the following statement.

sqlcmd -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE 'Whi%';"

Where
-d refers to the database that we are going to run our query
-q refers to the query which we are going to execute
The above statement will use windows authentication to run the query.
If you want to run the Query on a different server, using SQL Server credentials, then use the following command.

sqlcmd -S %Server% -U %UserName% -P %PassWord% -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

Where
-S refers to the database server
-U refers to the Username
-P refers to the password

sqlcmd -S MyServer -U sa -P password -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

sqlcmd -S localhost -U sa -P password -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

sqlcmd -S . -U sa -P password -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

Note:
-q will start the SQLCMD mode but does not exit sqlcmd when the query has finished running.
So in that case, if you want to exit the sqlcmd mode after running, use -Q [Caps Q]

sqlcmd -S MyServer -U sa -P password -d AdventureWorks2012 -Q "SELECT FirstName, LastName FROM Person.Person"

To run a Procedure/Sql Scripts from a .SQL File.
Now lets see how to run a storedprocedure/sql scripts stored in a file.
To do this, use the following command.

sqlcmd -S %Server% -U %UserName% -P %PassWord% -d %DataBase% -i "C:\SQLScripts\MySQLScript.sql" -I

sqlcmd -S %Server% -U %UserName% -P %PassWord% -d %DataBase% -i "C:\SQLScripts\MyProcedure.sql" -I

Where
-I
refers to QUOTED_IDENTIFIER in SQL

SET QUOTED_IDENTIFIER ON

Example:

sqlcmd -S MyServer -U sa -P password -d AdventureWorks2012 -i "C:\SQLScripts\MySQLScript.sql" -I

sqlcmd -S localhost -U sa -P password -d AdventureWorks2012 -i "C:\SQLScripts\MySQLScript.sql" -I

sqlcmd -S . -U sa -P password -d AdventureWorks2012 -i "C:\SQLScripts\MyProcedure.sql" -I

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s