Run Sql Command From Command Line

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.

Run Sql Command From Command Line

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)

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"

SQL From CMD

Output

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>"  

SQL From CMD

 sqlcmd -U myLogin -P myPassword -S MyServerName -d MyDatabaseName -I -i %0\..\DBScripts/StudentMaster.sql -o "C:\Some Folder\<file name>"  

Output in File

 
ID   FirmstName   StandardID  RollNumber  Gender
---- ----------   ---------- -----------  -----------
2    Nidhi            1      KAS001        Female        
3    Krishna          2      KAS008        Male
4    Kalyani          1      KAS002        Female
5    Nilanchal        2      KAS010        Male

(4 rows affected)


Completion time: 2024-07-21T11:00:42.4832413+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