Create SQLCMD Batch Files to be Interactive and Reused

By: Levi Masonde | Updated: 2023-06-14 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Tools

Problem

SQLCMD is a command line utility used for executing Transact-SQL (T-SQL) commands, stored procedures and SQL queries for Microsoft SQL Server. You can use T-SQL scripts and the SQLCMD utility for database administration. You can accomplish the same results using SSMS, but SQLCMD has a slight advantage regarding speed and low CPU usage. SQLCMD also has some properties you cannot find in SSMS, like script variables which enable you to execute a SQL script with parameters you can specify when calling the script from the command line.

Since batch files can execute SQL commands using the SQLCMD utility, SQLCMD can enable you to execute complex SQL commands stored in a file. This means SQLCMD is also portable and you can use the same SQL script on multiple machines or SQL Servers instances. So, the more you use SQLCMD to interact with your databases, the more scripts you can reuse.

Solution

In this tutorial, we will prepare the backend for an employee management application using the SQLCMD command line tool. You will use a prefilled Excel file with employee details and insert the data into a SQL Server database through a batch file.

Connecting to a SQL Server Instance with SQLCMD

To run SQLCMD from a command prompt in Windows, press Windows Key + R and type CMD.

Find SQL Servers on the Network with SQLCMD

Run the following SQLCMD command to find all instances on the network:

sqlcmd -L

This will return a SQL Server instance list of your environment. With my test environment, only one instance is returned. However, your results will vary in a larger environment with SQL Servers that are broadcasting.

Checking for SQL instances using SQLCMD.

Connect to SQL Server Instance with SQLCMD

Here is sample syntax to connect to a SQL Server instance with SQLCMD using SQL Server authentication:

sqlcmd -S <server_name>\<instance_name> -U <username> -P <password>

If you want to connect using Windows authentication, use the command below:

sqlcmd -S <server_name>\<instance_name> -E

Replace <server_name> with the name of the server.
<instance_name> with the name of the instance you want to connect to.
-U option is used to specify the username to connect with.
-P option is used to specify the password.
-E trusted connection to authenticate to SQL Server

Listing SQL Server Databases with SQLCMD

To list all the databases on an instance, use the following command:

sqlcmd -S DESKTOP-MSLVFI\KNITNETSERVER -E -Q "SELECT name from sys.databases"

This is a list of all databases on the SQL instance. The –Q option enables you to enter a SQL statement to execute.

Connect to a Specific SQL Server Database with SQLCMD

To connect to a specific database, use the following command:

sqlcmd -S <server_name>\<instance_name> -E -d <database_name>

Alternately, once connected, you can use the USE statement followed by the name of the database:

USE <database_name>

Running SQLCMD in Batch Mode

You can run your SQLCMD script via an executable batch file. This makes it easy to deploy the script on other environments.

This script will create a database and a table, then import CSV data to the database. For this tutorial, you can download this employee sample data to follow along. You will be importing Excel data to SQL Server.

To get started, create a SQL file named createDB_importCSV.sql and add the following code. You may need to adjust some of the values for your environment.

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '$(DatabaseName)')
BEGIN
CREATE DATABASE [$(DatabaseName)]
ON PRIMARY
(
    NAME = MyDatabase_Data,
    FILENAME = 'C:\SQLCMD\data\EmployeeSampleData\$(DatabaseName)Data.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = MyDatabase_Log,
    FILENAME = 'C:\SQLCMD\data\EmployeeSampleData\$(DatabaseName)Log.ldf',
    SIZE = 10MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 5MB
)
END
GO

 
USE [$(DatabaseName)];
GO

 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='$(TableName)' AND type='U')
BEGIN
CREATE TABLE [$(TableName)](
    Employee_ID VARCHAR(50) NOT NULL PRIMARY KEY,
    Full_Name VARCHAR(50) NOT NULL,
    Job_Title VARCHAR(50) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    Business_Unit VARCHAR(50) NOT NULL,
    Gender VARCHAR(50) NOT NULL,
    Ethnicity VARCHAR(50) NOT NULL,
    age INT,
    Hire_Date VARCHAR(50) NOT NULL,
    Annual_Salary VARCHAR(50) NOT NULL,
    Bonus VARCHAR(50) NOT NULL,
    Country VARCHAR(50) NOT NULL,
    City VARCHAR(50) NOT NULL,
    Exit_Date VARCHAR(50)
)
PRINT N'Created table';
END
GO

 
BULK INSERT [$(TableName)] FROM 'C:\SQLCMD\data\EmployeeSampleData\EmployeeSampleData.csv' WITH (FORMAT='CSV', FIRSTROW=2, FIELDTERMINATOR=',', ROWTERMINATOR='0x0a')

After creating the SQL file containing the SQLCMD commands, create a batch file named CreateDB.bat and add the following code:

@echo off
 
set /p DatabaseName="Enter the database name: "
set /p TableName="Enter the table name: "
 
sqlcmd -S DESKTOP-M5LVFVI\KNITNETSERVER -b -e -v Databasename="%DatabaseName%" -v TableName="%TableName%" -i createDB_importCSV.sql 2> error.log
 
if %errorlevel% neq 0 (
  echo There was an error running the script. Please check the error.log file for more information.
) else (
  echo The script ran successfully.
)

 
pause

Please note that the values for the database table's column names must match the column name of the CSV file. Open your project directory in your file explorer, locate the CreateDB.bat file, and double-click to open the file. It will trigger the CMD, and the user will be prompted to enter a database name and a table name. The script will create the database and table and import the CSV data to the table. The CMD prompts should look as shown below:

You should also be able to see that the database was created.

That's it. You have created a database named MSSQLTipsEmpDB and imported Excel data to it.

If you want to verify the creation of the database, open a CMD window and run the following command:

sqlcmd -S <server_name>\<instance_name> -E -Q "SELECT name from sys.databases"

As you can see, the database is listed as the last database.

Viewing Database Table

To quickly connect to an existing database and view the table content using SQLCMD, create an SQL file, ViewTable.sql, and add the following code:

USE $(DBname);
GO 
SELECT * FROM $(Tablename); 
GO

Then create a batch file, ViewTable.bat, and add the following code:

@echo off
 
set /p DatabaseName="Enter the database name: "
set /p TableName="Enter the table name: "
 
sqlcmd -S DESKTOP-M5LVFVI\KNITNETSERVER -b -e -v DBname="%DatabaseName%" -v TableName="%TableName%" -i ViewTable.sql 2> error.log
 
if %errorlevel% neq 0 (
  echo There was an error running the script. Please check the error.log file for more information.
) else (
  echo The script ran successfully.
)
 
pause

After saving the batch file, go to where you saved it in your file explorer and double-click the file to open it. The CMD will ask for the database and table variable inputs.

The results for the query will be shown on the CMD terminal as shown below:

Viewing database table using sqlcmd and batch.

Deleting Tables and Databases

Connect to the database using the methods shown earlier and delete the table using the following command:

DROP TABLE TableName;
GO

This can be used to delete entire database:

DROP DATABASE DatabaseName;
GO

Conclusion

Without SQL Server Management Studio, you can still manage your SQL instances using SQLCMD. You just learned how to create, update, and delete databases and their tables using the SQLCMD utility.

Batch files can be useful when performing repetitive or commonly used tasks. Therefore, you can build and test scripts on test servers and later execute the same files on other servers.

Next Steps

Learn how to use sqlcmd User Variables and Variable Precedence Examples.
You can also Execute SQL Server Script Files with the sqlcmd Utility.
Learn more about Using SQL Server sqlcmd scripting variables.
Learn more about Using SQLCMD to Execute Multiple SQL Server Scripts.
Execute Dynamic SQL commands in SQL Server.

About the author

Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-06-14

Sample Use Case for Using SQLCMD Utility Batch Files