HomeDatabaseSQL Server: Execute SQL Script Using SQLCMD Command Line

SQL Server: Execute SQL Script Using SQLCMD Command Line

SQLCMD command line Utility is an alternative way to execute SQL scripts using the command line. This allows us to execute SQL scripts without touching SQL Server Management Studio (SSMS) GUI.

The command line has multiple advantages in certain circumstances. Frequently, this process will save your time and mouse clicks. Plus, it is frequently used to run the same files on multiple sql servers with ease.

To follow along with my example, copy the following code and save it as CreateDBCompany.sql file under the C:\Sqlscripts\ folder.

USE Master
GO

IF EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = 'DBCompany' )
DROP DATABASE DBCompany
GO

CREATE DATABASE DBCompany
GO

USE DBCompany
GO

IF EXISTS ( SELECT [name] FROM sys.tables where [name]='Employee' )
DROP TABLE Employee
GO

CREATE TABLE Employee
(
EmpId int,
Fname varchar(40),
Lname varchar(40),
Hiredate datetime,
Salary int
)
GO

INSERT INTO EMPLOYEE VALUES (101,'Vishwanath','Dalvi','10/16/2011',23025);

 

Using the sqlcmd command

Now that our example is set, we will execute the CreateDBCompany.sql script file using SQLCMD command line.

1. Click Run and type the letters CMD. Hit OK, and a command prompt window will appear.

2. Now, locate the directory C:\sqlscripts\ using the following commands:

• Change the directory to C drive using cd c:\.
• Go to the folder by typing cd followed by a space, and then sqlscripts folder name such as cd sqlscripts.
• See the sqlscripts directory contents using dir. You can see our CreateDBCompany.sql file.

3. Here, we are using SQLCMD to connect to your local sql server database. Through this same process you may connect to any remote sql server on your network.

SQLCMD -S DALVI2\SQLSERVER2012 -E -i CreateDBCompany.sql

 

The -S switch is for the Server name and is the same one we use to connect using SSMS GUI. Connect to server option. In my case, it is DALVI2\SQLSERVER2012.
The -E switch is used for trusted connection.
The -i switch is for .sql script file name.

All switches are case sensitive. For more help on SQLCMD Utility switches, use the sqlcmd /? command.

You should now be able to go to SSMS and query the DBCompany database. You will see the employee table with the one record that we had previously created.

Vishwanath Dalvi
Vishwanath Dalvi
Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -

Most Popular

Recent Comments

Cleveland Furia on How to Clear Google Activity
Sid Wohlfarth on How to Clear Google Activity
Riley Magsayo on How to Clear Google Activity
สมัครรับเครดิตฟรี ทันที on How to improve Remote Desktop Protocol Performance
Neal Wangstad on How to Clear Google Activity
Slavcho Andov on How to install Ubuntu
Slavcho Andov on How to fix bad sectors
Sheraz Ali on How to Backup Android