In this short article I will introduce the possibility to run a sql script outside of SQL management studio using SQL server command line utility (sqlcmd for short). This utility allows you to work with SQL server using the command line. The use are obvious and include working with SQL from different applications.

How to execute SQL statement via sqlcmd?

Below I will describe the steplist. As a preparation let’s create a table “Biportal_sqlcmd” using CREATE script and insert the value “Hello world”. Script:

USE [Temp];
CREATE TABLE Biportal_Test (
[Text] VARCHAR(255)

INSERT INTO Biportal_Test (
VALUES(‘Hello world’);

We will run this script later with sqlcmd app.

Steplist for using sqlcmd

1) Run the command prompt – Search for “Cmd” – Command Prompt

Spuštění cmd

2) First, let’s try to run the sqlcmd utility script to retrieve all the parameters we can use. We can do this by running the sqlcmd -?

Parametry SQLCMD

As a result we can see that there are a number of parameters. The most important ones are:

-S the name of the SQL server instance we want to connect to
-U Username (not needed if connecting under windows auth)
-P Password (not needed if connecting under windows auth)
-Q Query we want to run

3) Now it’s time to run the final query. Use the -S and -Q parameters. The whole SQL statement could look something like this:

sqlcmd -S SQL-13 -Q “USE [Temp];CREATE TABLE Biportal_Test ([ID] INT IDENTITY(1,1) PRIMARY KEY, [Text] VARCHAR(255));INSERT INTO Biportal_Test ([Text]) VALUES(‘Hello world’);”

After running, we can see the command has been executed successfully (1 row affected)

sqlcmd - spuštění příkazu v cmd

4) At the end let’s do a short test if the sql script has been actually executed by running SELECT * FROM biportal_test. We can see the table contains 1 row with ID = 1 and value = “Hello World”

sqlcmd - test prikladu

5/5 - (1 vote)

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *