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 (
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Text] VARCHAR(255)
);
INSERT INTO Biportal_Test (
[Text]
)
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
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 -?
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)
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”
