how to execute SQL statements in command prompt (CMD)

Learn how to execute sql statements in command prompt (cmd) with practical examples, diagrams, and best practices. Covers sql, sql-server, cmd development techniques with visual explanations.
Tags:
Categories:

Executing SQL Statements from the Command Prompt (CMD)

Executing SQL Statements from the Command Prompt (CMD)

Learn how to interact with SQL databases directly from your Windows Command Prompt, enabling quick queries, script execution, and automation for SQL Server and other databases.

The command prompt (CMD) in Windows provides a powerful interface for interacting with various system components, including databases. For SQL Server and other relational databases, executing SQL statements directly from CMD can be invaluable for scripting, automation, and quick administrative tasks without needing a full-fledged GUI client. This article will guide you through the process, focusing on SQL Server but also touching upon general principles applicable to other database systems.

Prerequisites and Setup

Before you can execute SQL statements from the command prompt, you need to ensure you have the necessary tools and configurations in place. For SQL Server, the primary tool is sqlcmd.exe, which is typically installed with SQL Server Management Studio (SSMS) or SQL Server client tools. Make sure it's accessible from your system's PATH environment variable.

Connecting to SQL Server using sqlcmd

sqlcmd is a command-line utility for executing Transact-SQL statements, system procedures, and script files. It's a versatile tool for database administration, development, and automation. You can connect to a SQL Server instance using Windows Authentication or SQL Server Authentication.

sqlcmd -S YourServerName\YourInstanceName -E

Connect to a named instance using Windows Authentication.

sqlcmd -S YourServerName -U YourUsername -P YourPassword

Connect to a default instance using SQL Server Authentication.

A flowchart diagram illustrating the SQLCMD connection process. Start node 'Open CMD', followed by 'Choose Authentication (Windows/SQL Server)'. If Windows Auth: 'Execute sqlcmd -S Server -E'. If SQL Server Auth: 'Execute sqlcmd -S Server -U User -P Pass'. Both paths lead to 'Connected to SQL Server'.

SQLCMD Connection Flow

Executing Single SQL Statements

Once connected, you can execute individual SQL statements directly within the sqlcmd interactive session or by passing them as arguments to the sqlcmd command.

sqlcmd -S YourServerName -U YourUsername -P YourPassword
1> SELECT GETDATE();
2> GO
3> USE YourDatabase;
4> SELECT * FROM YourTable;
5> GO
6> EXIT

Executing multiple statements in an interactive sqlcmd session.

sqlcmd -S YourServerName -U YourUsername -P YourPassword -Q "SELECT name FROM sys.databases;"

Executing a single query and immediately exiting.

Executing SQL Script Files

For more complex operations or multiple statements, it's best to store your SQL commands in a .sql file and execute the entire script. This promotes reusability and version control.

USE YourDatabase;
GO

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE DEFAULT GETDATE()
);
GO

INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Employees (FirstName, LastName) VALUES ('Jane', 'Smith');
GO

SELECT * FROM Employees;
GO

A sample SQL script file.

sqlcmd -S YourServerName -U YourUsername -P YourPassword -i C:\path\to\your\script.sql -o C:\path\to\output.txt

Executing example.sql and directing output to a text file.

A sequential workflow diagram showing 'Create SQL Script File (.sql)', then 'Open Command Prompt', then 'Execute sqlcmd -i script.sql', leading to 'SQL Statements Executed', and finally 'Output to Console/File'.

Workflow for executing SQL script files via CMD.

Handling Output and Errors

sqlcmd provides options to control how output is displayed and how errors are handled. You can redirect output to a file, specify error levels, and more.

sqlcmd -S YourServerName -E -Q "SELECT @@VERSION" -o C:\temp\version.txt -h -W

Executing a query, outputting to a file (-o), removing headers (-h), and removing trailing spaces (-W).

1. Step 1

Open your Command Prompt (CMD) as an administrator to ensure you have the necessary permissions.

2. Step 2

Identify your SQL Server instance name (e.g., SERVERNAME\SQLEXPRESS) or IP address and port.

3. Step 3

Choose your authentication method: Windows Authentication (-E) or SQL Server Authentication (-U username, -P password).

4. Step 4

To execute a single query, use the -Q option: sqlcmd -S YourServer -E -Q "SELECT DB_NAME();".

5. Step 5

To execute a SQL script file, use the -i option: sqlcmd -S YourServer -U User -P Pass -i C:\Scripts\MyScript.sql.

6. Step 6

Review the output in the CMD window or the specified output file for results and any errors.