Run Sql Command From Command Line

Kailash Chandra Behera | Sunday, June 16, 2024

Introduction

In my previous blog Run SQL Scripts from batch file, we learned how to execute a SQL Query or SQL Commands using batch file. Here we will learn to execute sql commands in the command prompt.

Getting Started

We will not discuss here for creating sql queries, if you do not know writing SQL queries then I suggest that you refer to other blogs before continuing this.

A utility is provided by Microsoft which helps to run sql statements in the command prompt(cmd), the utility name is sqlcmd utility. If the SQL server is already installed in your system then sqlcmd utility would have installed. Because it is being released with the installer package for SQL Server. Run the below command line to determine the currently installed version or know whether it was installed or not.

 sqlcmd -?   
Output
 Microsoft (R) SQL Server Command Line Tool  
 Version 15.0.2000.5 NT  

If the utility is not installed then download from the below link, There are two types of sqlcmd as given below.

  1. sqlcmd(ODBC)

    This is the default utility that gets installed with the SQL Server installation package and lets you enter Transact-SQL statements, system procedures, and script files.

  2. sqlcmd(GO)

    The go-sqlcmd utility is designed to be a more modern command line utility. It is designed to do everything the old sqlcmd did, but include additional options for Azure Active Directory authentication, more environment configurations, more logging/tracing, and get results in a vertical format that is easier to read. We will discuss it later in another post.

Downloads

sqlcmd (ODBC) for SQL Server (x64)
sqlcmd (ODBC) for SQL Server (x86)
sqlcmd(GO)

SQLCMD example

Using the command prompt we can run SQL Queries as well as sql files and the most important is like SQL editor(SSMS), we can save the results in a file. The following sql code examples explain how to use sqlcmd in the command prompt.

Run SQL Query in the command prompt

Below is the command line Script for SQL where you can run direct the a sql query.

 sqlcmd -U myLogin -P myPassword -S MyServerName -d MyDatabaseName -Q "SQL Query"  

For example, let's say you want to run the following sql query from cmd.

SELECT 
      Convert(NVARCHAR(10),FirmstName) FirstName,
      Convert(NVARCHAR(10),LastName) LastName,
      Convert(NVARCHAR(10),RollNumber) RollNumber 
FROM  StudentMaster

Examples of SQL Query

Then the sql cmd line will be like.

sqlcmd -U sa -P sa@321 -S KB\SQLEXPRESS -d KAS -Q "SELECT Convert(NVARCHAR(10),FirmstName) FirstName,Convert(NVARCHAR(10),LastName) LastName,Convert(NVARCHAR(10),RollNumber) RollNumber FROM StudentMaster"
Output

sql from cmd
SQL From CMD

Run a Sql Query From SQL File

The following SQL command lines will open SQL File StudentMaster.sql and will run the SQL codes that is available inside it.

 sqlcmd -U myLogin -P myPassword -S MyServerName -d MyDatabaseName -I -i %0\..\DBScripts/StudentMaster.sql 

Save Results in a File

 sqlcmd -U myLogin -P myPassword -S MyServerName -d MyDatabaseName -Q "SELECT FirstName, LastName,RollNumber From StudentMaster" -o "C:\Some Folder\<file name>"  
 sqlcmd -U myLogin -P myPassword -S MyServerName -d MyDatabaseName -I -i %0\..\DBScripts/StudentMaster.sql -o "C:\Some Folder\<file name>"  
Output in File
 FirstName  LastName RollNumber      
 ----------    ----------   ----------  
 Nidhi      Behera    KAS001  
 Krishna    Behera    KAS008  
 Kalyani    Behera    KAS002  
 Nilanchal  Behera    KAS010  
 (4 rows affected)  
 Completion time: 2024-06-17T11:01:40.1358358+05:30  

The laters used with the prefix - are called SQL Command options, details of the used options are given below.

SQL Command Options

  1. -S Specifies the instance of SQL Server to which to connect. It sets the sqlcmd scripting variable SQLCMDSERVER.

  2. -d Issues a USE db_name statement when you start sqlcmd.

  3. -U Is the login name or contained database user name. For contained database users, you must provide the database name option (-d).

  4. -P Is a user-specified password. Passwords are case-sensitive.

  5. -i Identifies the file that contains a batch of SQL statements or stored procedures.

  6. -o Identifies the file that receives output from sqlcmd.

  7. -v Creates a sqlcmdscripting variable that can be used in a sqlcmd script.

Thanks