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):

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:

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:

The actual full path

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:

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 P@$$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.

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:

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 P@$$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:

Newly created output file

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

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.