Articles → SQL SERVER → Execute TSQL Statements Using Sqlcmd Utility

Execute TSQL Statements Using Sqlcmd Utility

In this article, we will execute T-SQL statements using the SQLCmd utility.

What Is Sqlcmd Utility?

SQLCmd is a command line utility for executing T-SQL statements.

Syntax

Sqlcmd –s <server_name> -i <script_file_name>

Creating Script Files

For this article, I will create three script files and execute them using the SQLCmd utility.

Script 1

Use [Master] Go CREATE DATABASE gyansangrah;

Script 2

Use [gyansangrah] 
  Go 
  Create table Test (
  TestId Int Identity(1, 1), 
  TestName varchar(50)
)

Script 3

Use [gyansangrah]
Go
Insert into Test values('Test1')

Save all the files on the particular location.

Create A Batch File

First, open the notepad (or any text editor) and enter the following commands.

sqlcmd -S .\SQLserver  -i C:\temp\scripts\create_database.sql

sqlcmd -S .\SQLserver -i C:\temp\scripts\create_table_Test.sql 

sqlcmd -S .\SQLserver  -i C:\temp\scripts\Insert_Into_Test.sql

Save this file as "Script.bat" (file extension should be .bat).

Run The Batch File

Open the run popup (windows + R)
Type cmd and press enter
Command window open up
Go to the path where the batch file is stored (using cd batch_file_path)
Enter Script.bat and press enter

Once you press enter all the script gets executed.

Output

Picture showing the database and table created after the batch file is executed

Click to Enlarge

Adding Output In A File

We can also add the output of query execution in a file so that we can check if the command is executed successfully or not.

Syntax

Sqlcmd –s <server_name> -i <script_file_name> -o <output_file_path>

So, the preceding code will be replaced by

sqlcmd -S .\SQLserver -i C:\temp\scripts\create_database.sql -o C:\temp\scripts\create_database.txt

sqlcmd -S .\SQLserver -i C:\temp\scripts\create_table_Test.sql -o C:\temp\scripts\create_table_Test.txt

sqlcmd -S .\SQLserver -i C:\temp\scripts\Insert_Into_Test.sql -o C:\temp\scripts\Insert_Into_Test.txt

Output

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Tuesday, July 12, 2016

Updated On -	Friday, September 23, 2016

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**