Click here to Skip to main content
15,794,633 members
Articles / Programming Languages / SQL

Programming DOS Shell

Rate me:
Please Sign up or sign in to vote.
4.16/5 (15 votes)
1 Apr 2015CPOL4 min read 30.6K   195   12   4
This tip describes steps to learn DOS batch programming.

Sample Image - maximum width is 600 pixels


Professionally, I am a database and BI consultant. Mostly, I work on SQL Server and BI related technology, but traditionally I assume myself as a programmer. I used a different scripting language to solve a specific problem many times in my work profile. No matter what BI platform you are using, sometimes, you require to develop such script to make your job easy. It could be for cleansing dirty files, managing file system related task, handling any executable utility through command line, automation of any process, etc. All these require script writing skills. This post is based on learning programming skills for DOS batch script. Although DOS is nowhere used but its scripting and commands are powerful too. I will be explaining different tokens to learn DOS batch script through out this post.


DOS batch script uses DOS commands with many programming stuff. It becomes extremely powerful when programming is applied over these commands and commands output. Users only require basic programming skills with excellent knowledge of DOS commands to learn batch programming. As mentioned above, I am a BI and database consultant, so my research and article contents are based on that experience only.

Using the Code

Launch command prompt and start DOS batch programming. you can create batch script in Notepad++.batch script has extension .bat.

Demo Operator in DOS Batch Programming

@echo off
rem demo all arithmetic operator

rem set /p is used for prompt reading on variable
set /p val1="Enter first value :"
set /p val2="Enter second value :"

echo ********* arithmetic operator demo ************
rem set /a is used for arithmetic calculation
set /a res=%val1%+%val2%
echo Sum of %val1% and %val2% is %res%

set /a res=%val1%-%val2%
echo Subtraction of %val1% and %val2% is %res%

set /a res=%val1%*%val2%
echo Multiplication of %val1% and %val2% is %res%

set /a res=%val1%/%val2%
echo Division of %val1% and %val2% is %res%

set /a res=%val1%%%val2%
echo Modulo division of %val1% and %val2% is %res%

echo ********* relational operator demo @@ geq,gtr,equ,lss,leq @@************
if %val1% gtr %val2% (
echo %val1% is greater than %val2%
) else (
if %val1% lss 10 (
echo %val1% is even less than 10
) else (
echo %val1% is less than %val2%

Image 2

In the above operator demo batch script, I have described all arithmetical operator and relational operator used in batch programming. It will accept 2 values from user and perform arithmetical and relational calculation on it. I have used set /a command to perform arithmetical operation and set /p to accept input from user.

Demo if-else in DOS Batch Programming

@echo off
rem check for leap year using if else
set /p yval= "Enter year value :"
set /a res=%yval%%%4 
if %res%==0 (
echo "%yval% is a leap year"
) else (
echo "%yval% is not a leap year"

rem check if file is available or not
set filename=savecustomer1.sql
if exist %filename% (
echo File exist
) else (
echo File does not exist

rem check exit status of last running command
DEL test.txt
if %ERRORLEVEL%==1 (
echo last operation was successful
) else (
echo problem in last operation

Image 3

In if-else demo, I have explored how if-else works in DOS batch programming. I have used 3 examples to explain it. In my first example, I am accepting year value from user and displaying result whether given year is leap year or not. In my second example, I am checking the existence of a file using if-else. In my last example, I have used ERRORLEVEL system variable to get exit status of last executed command so that user can check whether last command execution was a success or a failure. For each success execution, it returns 0 and 1 for failure.

Demo for Loop in DOS Batch Programming

@echo off
echo *****Simple numeric for loop*****
FOR /L %%G IN (2,1,5) DO echo %%G 

echo *****for loop working on collection*****
FOR %%G IN (Sun Mon Tue Wed Thur Fri Sat) DO echo %%G 

echo *****for loop /R reading files of current directory recursively *****
For /R %%G in (*.*) do Echo "%%G" 

echo ***** looping file contents @@@ Reading comma separated file @@@@******
FOR /F "tokens=1,2,3 delims=," %%G IN (persondata.txt) DO @echo %%G %%H %%I

Image 4

In the above for loop demo, I have explained 4 examples of using for loop. In my first example, I have used a simple loop where iteration starts from 2 and reaches upto 5 incremented by 1 every time. In my second example, I have described for loop iterating through a string collection. It will display all lists available in for loop collection. In my third example, I have used for loop to recursively read all files of current directory. In my final example, I am reading a comma separated file persondata.txt and displaying all columns.

Demo SQL Query Execution Using DOS Batch Shell

@echo off
Rem execute SQL query from batch file
sqlcmd -S .\sql2012 -Q "insert into SQLGD.dbo.customer values(1,'Ajit Kumar Thakur')"

Rem execute SQL query from SQL file
sqlcmd -S .\sql2012 -i SaveCustomer.sql

Image 5

In the above shell script demonstration, I have used SQLCMD command line utility to connect SQL Server database. I have used Windows authentication mode to connect with database server. One can use another different option with SQLCMD to perform database related operation from DOS batch script. In my first example, I have executed insert script, while in my other example I am executing SQL query available in SaveCustomer.sql file.

Demo Execution of Multiple DOS Batch Script from Another Batch Script

@echo off
FOR /L %%G IN (1,1,200) DO cmd /C demoexecsqlquery

Image 6

In the above example, I am calling demoexecsqlquery.bat file 200 times. So 200 separate command sessions will generate and execute scripts inside it.

Demo of Incremental File Loading through DOS Batch Script

@echo off
rem processing incremental load using batch file
echo processing incremental load

For /R %%A in (demofileprocessing\curversion\*.txt) do (
	set %dupflag%=0
	For /R %%B in (demofileprocessing\preversion\*.txt) do (
	rem compare current version file with previous version file repository
	fc %%A %%B > 0
	rem check for same data file repeating in new version
	if %errorlevel%==1 (
			set %dupflag%=1
	 if %dupflag%==1 (
		rem copy incremental file in separate location
		copy %%A demofileprocessing\incrementalfile		
	 ) else (
		rem copy duplicate file in other location
		copy %%A demofileprocessing\duplicatefile	

Image 7

In the above batch programming example, I am reading all files from curversion folder, checking if the same file was available in the previous version or not. If it is new file, then copy file into incrementalfile folder, otherwise copy it into duplicatefile folder.

Points of Interest

This post explains programming tokens of DOS batch programming. It also describes how we can use SQLCMD in batch programm and how programming flow of DOS batch script works. I hope my effort will help someone in learning of DOS batch script. Happy scripting...


  • 31st March, 2015: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Database Developer Microsoft
India India
I am Ajit Kumar Thakur. I am Consultant in Microsoft Global Delivery India. I work on Database (SQL Server), BI (SSAS, SSRS,SSIS),and Windows Powershell. I have 8 years of experience in Database and BI project development.

Comments and Discussions

GeneralMy vote of 5 Pin
Duff 112-Jan-20 10:04
Duff 112-Jan-20 10:04 
QuestionFive Points Pin
eslipak7-Apr-15 10:37
professionaleslipak7-Apr-15 10:37 
GeneralMy vote of 5! Pin
jediYL2-Apr-15 16:28
professionaljediYL2-Apr-15 16:28 
GeneralRe: My vote of 5! Pin
Ajit Kumar Thakur Microsoft2-Apr-15 23:01
professionalAjit Kumar Thakur Microsoft2-Apr-15 23:01 

Ajit Kumar Thakur

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.