The sqlcmd utility is a command-line tool that comes with Microsoft SQL Server. It allows users to execute SQL queries, perform database management tasks, and automate database operations directly from the command prompt. This tool is particularly useful for database administrators and developers who need to interact with SQL Server without using a graphical interface like SQL Server Management Studio (SSMS).

Getting Started with sqlcmd

Before using sqlcmd, ensure that it is installed on your Windows machine. It is typically included with SQL Server installations, but you can also install it separately as part of the SQL Server Command Line Utilities.

Basic Usage

To use sqlcmd, open the Command Prompt and type sqlcmd followed by various options and arguments to connect to a SQL Server instance and execute commands.

Example 1: Connecting to a SQL Server Instance

To connect to a local SQL Server instance using Windows Authentication, use the following command:

sqlcmd -S localhost -E

• -S specifies the server name. localhost is used for a local SQL Server instance.
• -E indicates that Windows Authentication is used.

Example 2: Executing a Simple Query

Once connected, you can execute SQL queries. For example, to select all records from a table named Employees in the HR database:

sqlcmd -S localhost -E -d HR -Q "SELECT * FROM Employees"

• -d specifies the database name.
• -Q allows you to run a query directly from the command line.

Example 3: Running a Script File

You can also execute SQL scripts stored in a file. Suppose you have a script file named CreateTable.sql:

sqlcmd -S localhost -E -d HR -i "C:\Scripts\CreateTable.sql"

• -i specifies the input file containing SQL commands.

Advanced Usage

Example 4: Using Variables in sqlcmd

You can define and use variables within your SQL scripts. Here's how you can pass a variable to a script:

sqlcmd -S localhost -E -d HR -v EmployeeID=123 -Q "SELECT * FROM Employees WHERE ID=$(EmployeeID)"

• -v is used to define a variable. In this example, EmployeeID is set to 123.

Example 5: Exporting Query Results to a File

To save the output of a query to a file, use the -o option:

sqlcmd -S localhost -E -d HR -Q "SELECT * FROM Employees" -o "C:\Output\Employees.txt"

• -o specifies the output file path.

Conclusion

The sqlcmd utility is a powerful tool for managing SQL Server databases from the command line. It provides flexibility and automation capabilities, making it an essential tool for database administrators and developers working in a Windows environment.