Execute SQL script from command line

Learn execute sql script from command line with practical examples, diagrams, and best practices. Covers sql, sql-server, batch-file development techniques with visual explanations.
Tags:
Categories:

Executing SQL Scripts from the Command Line

Executing SQL Scripts from the Command Line

Learn how to automate SQL script execution directly from your command line, enhancing your database management and deployment workflows.

Executing SQL scripts from the command line is a fundamental skill for database administrators, developers, and anyone involved in automating database tasks. This method provides flexibility for scripting, continuous integration/continuous deployment (CI/CD) pipelines, and scheduled maintenance. This article will guide you through the process, focusing on SQL Server using sqlcmd.

Why Use Command Line for SQL Scripts?

Automating SQL script execution offers several significant advantages. It allows for unattended operations, making it ideal for scheduled tasks like daily data loads, backups, or schema updates. Furthermore, it integrates seamlessly with batch files, PowerShell scripts, and CI/CD tools, enabling robust and repeatable deployment processes. This approach minimizes manual intervention, reduces human error, and ensures consistency across environments.

Using sqlcmd for SQL Server

sqlcmd is a command-line utility for executing Transact-SQL statements, system procedures, and script files. It's an essential tool for interacting with SQL Server without a graphical interface. You can connect to a local or remote SQL Server instance, execute a single query, or run an entire script file.

sqlcmd -S localhost -d master -U sa -P YourStrongPassword! -Q "SELECT name FROM sys.databases;"

Executing a simple query directly using sqlcmd.

Executing a SQL Script File

The real power of sqlcmd comes from its ability to execute entire SQL script files. This is particularly useful for deploying database changes, running migrations, or performing complex data manipulations. You can specify an input file using the -i flag and optionally redirect output to a file using the -o flag for logging purposes.

USE MyDatabase;
GO
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DateOfBirth DATE
);
GO

Example SQL script to create a table.

sqlcmd -S localhost -d MyDatabase -U sa -P YourStrongPassword! -i "C:\Scripts\create_table.sql" -o "C:\Logs\create_table_log.txt"

Executing create_table.sql and logging output.

A flowchart diagram showing the process of executing a SQL script from the command line. Steps include: Start, Prepare SQL Script, Open Command Prompt, Execute sqlcmd command, Log Output, Review Log, End. Use light blue boxes for actions, green circles for start/end, and arrows for flow. Clean, technical style.

Workflow for executing a SQL script via command line.

Advanced sqlcmd Options

sqlcmd offers a variety of advanced options for specific scenarios:

  • -v (Scripting Variables): Pass variables to your SQL scripts, allowing for dynamic behavior without modifying the script file itself.
  • -E (Trusted Connection): Use Windows Authentication instead of SQL Server Authentication.
  • -r (Error Reporting): Control how errors are reported.
  • -b (Abort on Error): Exit immediately if an error occurs during script execution.

These options provide granular control, enabling you to tailor sqlcmd to complex automation requirements.

USE $(DatabaseName);
GO
INSERT INTO $(TableName) (FirstName, LastName) VALUES ('$(FName)', '$(LName)');
GO

SQL script demonstrating the use of scripting variables.

sqlcmd -S localhost -E -i "C:\Scripts\script_with_variables.sql" -v DatabaseName="MyDatabase" TableName="Employees" FName="John" LName="Doe"

Executing a script and passing variables via sqlcmd.

1. Step 1

Prepare your SQL script: Ensure your .sql file contains the commands you want to execute, including GO batch terminators where necessary.

2. Step 2

Identify SQL Server details: Note your server name (or IP address), database name, and authentication credentials (username/password or use Windows Authentication).

3. Step 3

Open Command Prompt or PowerShell: Navigate to the directory where your script is located, or provide the full path to the script.

4. Step 4

Construct the sqlcmd command: Assemble the command using -S for server, -d for database, -U and -P for credentials (or -E for trusted connection), -i for input file, and optionally -o for output log.

5. Step 5

Execute the command: Press Enter to run the sqlcmd command.

6. Step 6

Review the output/log file: Check the console output or the specified log file (-o) for any errors or messages to confirm successful execution.