Using SQLCMD To Write SQL Server Data

Welcome back to the second part in our series on how to write SQL Server Data using the command line. In our first part of this database tutorial programming guide, we learned how to find SQLCMD.EXE, convert data to a CSV file format, and create a read-only user for MSSQL Server. In this part, we will learn to write and save data to an SQL Server database using SQLCMD.EXE.

If you missed the first part, you can read it here: Using the Command Line to Write SQL Server Data.

Using SQLCMD.EXE for SQL Code

At this point, we are ready to get data at the command line. To execute the code, we need to open a Windows Command Prompt and change to the folder where the full QUOTENAME code resides. If you do not know how to do this, follow the process below.

Finding the SQL Code File

To get this path, right-click on the filename heading in SSMS (the purple rectangle), and select Open Containing Folder (the red rectangle):

SQL Code directory

Finding the directory with the SQL code

A File Explorer window will appear. In this window, click in the address bar to convert the path to a format that will work with folder navigation in the Windows Command Prompt (for you history buffs, this involves what used to be DOS commands, and folders in Windows used to be called directories in DOS). In the example below, you would click where the red rectangle is, and the purple rectangle is the entry for the code file from SSMS:

SQL Code Folder

The folder containing the code

Upon clicking the address bar, it will change into the path. Select the entire path (in the red rectangle) and right click on it to select Copy:

SQL file filepath

The actual full path

Coy the SQL Filepath

Copying the full path, after right-clicking

Read: Best Relational Database Software

Accessing the Command Prompt for SQL Code

With the full path copied, open a Windows Command Prompt by pressing the Start button and typing in the letters CMD, then pressing the Enter key.

Upon seeing the black Windows Command Prompt box, enter cd and a space and right-click the mouse. The full path should now be pasted. Press the Enter key. The Command Prompt window should look something like the following:

How to change directory in Command Promopt

Command Prompt after switching to directory with code.

The folder you see will vary depending on where you saved the file. For this example, the filename of the code file is CSV Export Demo – select all employees quotename.sql.

The listing below contains the command line to SQLCMD.EXE, which will print the output to the Windows Command Prompt:

sqlcmd -S .\SQLEXPRESS -U exporter_readonly -P [email protected]$$w0rd123 -s , -W -h -1 -i "CSV Export Demo - select all employees quotename.sql"

Before executing this, it is important to know what these parameters, or switches, do.

Parameter

Purpose or Function

-S .\SQLEXPRESS

Opens the SQLEXPRESS instance of SQL Server on your local server.  The local server is denoted by a period (dot) .

The value of this parameter will vary depending on which SQL Server you attempt to connect to, as well as where it may be on your network.

-U exporter_readonly

Connect using the exporter_readonly SQL Server account.

-P [email protected]$$w0rd123

Connect using the password specified.  Be mindful of the important note below regarding the use of characters which need to be escaped in the password!

-s ,

Use a comma to separate the values.  This is the whole point of this demonstration!

-W

Remove whitespace after each value.

-h -1

Chop off 1 line from the top of the output because that is the header.

-i filename

Read the SQL Code from the filename specified.

If you used characters in your password which need to be escaped in the Command Prompt window, you will need to do this before the command will work properly. For the purposes of this demonstration, no characters which needed escaping were used in the password.

Entering this command into the Command Prompt Window and pressing the Enter key gives us the following results:

SQL Command Prompt

Seeing the records in the command window

The above command uses the caret (^) convention to split the command between lines for better readability. The command could be a single line if you choose. If you see a line that says X records affected, it means you are missing set nocount on in your SQL Code. This may break CSV processing.

If you get an error indicating that the SQLCMD.EXE command cannot be found, then you will need to prefix the command with the full path to the file as determined at the top of this article. So instead of:

sqlcmd (rest of command)

Use:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe (rest of command)

Read: Learn the Basics of Extracting Data in SQL in Five Minutes

Saving SQL Data to a CSV File

Saving the data to a CSV file can be done by specifying the -o parameter and a filename:

sqlcmd -S .\SQLEXPRESS -U exporter_readonly -P [email protected]$$w0rd123 -s , -W -h -1 -i "CSV Export Demo - select all employees quotename.sql" -o output-file.csv

Note that the listing above is intended to be a single line. The -o operator will overwrite any file specified if that file already exists. Redirect operators such as 1> or > will not work with SQLCMD.EXE

Note how the file output-file.csv appears in the File Explorer window now:

SQL Output File

Newly created output file

Opening this file in a code editor like Notepad++ yields:

Final SQL Output in Notepad++

The final output

Conclusion to Command Line SQL Tutorial

This demonstration only scratches the surface of what SQLCMD.EXE can be used to do. As stated before, SQLCMD.EXE can execute any kind of SQL code, even code which writes to the database. There are times when this functionality can be more useful than using the SQL Server Agent depending on the business use case.

Beyond that, commands in the Windows Command prompt can be scripted by way of Windows Batch Files. Batch Files in Windows have a very rich programming syntax of their own, but they can provide very robust solutions in their own right. These Batch Files can then be scheduled via the Windows Task Manager so they can run unattended on a schedule. Any business process which requires data to be uploaded to an external source can make use of Batch Files which contain the call to SQLCMD.EXE and the creation of an output file, with an additional call to another software solution (e.g., WinSCP) to perform the upload.

Additionally, calls to SQLCMD.EXE can replace old SQL Server Integration Services (SSIS) Packages which may not be compatible with later versions of SQL Server. SQL Code files are also much easier to maintain as they do not require an IDE or a diagram to develop.

Read more database programming and database administration tutorials.

Phil Hajjar
Phil Hajjar
Phil is usually seen making things work together that shouldn’t be, but need to be. He describes himself as a marriage counselor for software and other technology systems. He appropriated this moniker way back in college as he first experimented with making disparate software work together back then, and he continues doing so in his over 20 years of professional IT experience now.

More by Author

Must Read