askvity

How to Run SQL in SQL Command Line?

Published in SQL Command Line 3 mins read

To run SQL commands using the command line, you typically use the sqlcmd utility, a command-line tool provided with Microsoft SQL Server that allows you to execute Transact-SQL batches.

Here's a breakdown of how to use sqlcmd based on common practices and the provided reference:

Accessing the SQL Command Line (sqlcmd)

The first step is to open your command prompt and launch the sqlcmd utility. This tool allows you to interact directly with your SQL Server instance.

Steps to Launch sqlcmd

Follow these steps to open the command prompt and start the sqlcmd session:

  1. Open the Run dialog: On the Windows Start menu, select Run.
  2. Type cmd: In the Open box that appears, type cmd.
  3. Launch Command Prompt: Select OK or press the ENTER key to open a Command Prompt window.
  4. Type sqlcmd: At the command prompt, type sqlcmd.
  5. Press ENTER: Press the ENTER key.

Once sqlcmd starts, you might need to specify connection details (like server name, authentication method) depending on your setup. For example, to connect using Windows Authentication (trusted connection), you might use sqlcmd -S YourServerName -E. To use SQL Server Authentication, you'd use sqlcmd -S YourServerName -U YourUserName -P YourPassword. If you just type sqlcmd and press Enter, it attempts to connect to the default local instance using Windows Authentication.

Executing SQL Statements Interacttively

Once sqlcmd is running and connected to a SQL Server instance, you will see a prompt (typically 1>). This indicates that sqlcmd is ready to accept your SQL input.

  • Type your SQL commands: Enter your Transact-SQL statements line by line.
  • Execute with GO: After typing one or more SQL statements, type GO on a new line and press ENTER. The GO command signals sqlcmd to send the accumulated batch of commands to the SQL Server for execution.

Example:

SELECT name FROM sys.databases;
GO
SELECT COUNT(*) FROM sys.tables;
GO

In this example, the first GO executes the SELECT name FROM sys.databases; statement, and the second GO executes the SELECT COUNT(*) FROM sys.tables; statement.

Ending the sqlcmd Session

When you are finished executing commands, you need to exit the sqlcmd utility.

  • Type EXIT: At the sqlcmd prompt, type EXIT.
  • Press ENTER: Press the ENTER key.

This will close the sqlcmd session and return you to the standard Windows Command Prompt.

Running SQL Script Files

Beyond interactive use, sqlcmd is often used to execute entire SQL script files (.sql files) directly from the command line without entering the interactive prompt.

You can do this using the -i option, specifying the path to your script file. You can also redirect output to a file using the -o option.

Example:

To execute a script located at C:\Scripts\setup_database.sql against YourServerName using Windows Authentication and save the output to C:\Logs\setup_output.txt:

sqlcmd -S YourServerName -E -i "C:\Scripts\setup_database.sql" -o "C:\Logs\setup_output.txt"

This method is particularly useful for automating database tasks, deployments, or running large scripts.

Related Articles