Run all SQL files in a folder (directory)

To run multiple sql files in a sequence do this


1>Put all your .sql files that you want to execute in one folder


2>Name them alphabetically in the order you want them to be executed. (For instance if I have two sql files, I will name the sql file that I want to be executed first as 1.sql and second as 2.sql). The reason is the batch file below picks up files in the ascending order of their names.


THE SCRIPT BELOW CAN ONLY BE EXECUTED ON MACHINES THAT HAVE SQL SERVER INSTALLED


3>  In the green text below, replace the servername with the name of your server and the database name with the name of the database. Then copy the text into notepad and save it with a .bat extension. Note that the script is slightly different for windows xp and windows 7 machines


For windows xp use
@echo off
ECHO %USERNAME% started the batch process at %TIME%  >output.txt


FOR %%? in ("*.SQL") DO 
 (
sqlcmd.exe  -S servername -E   -d  DatabaseName  -i %%? >>output.txt
)
pause

For Windows 7 use
@echo off
ECHO %USERNAME% started the batch process at %TIME%  >output.txt


for %%f in (*.sql) do (
sqlcmd.exe  -S servername -E   -d DatabaseName -i %%f >>output.txt
    )
pause


4>Double click the the .bat file and it will execute all the .sql files in that folder. It will write the errors (if any) into a file called output.txt
Check out the image below for a sample scenario

To execute queries one by one in any order you want, use this



@echo off
ECHO %USERNAME% started the batch process at %TIME%  >output.txt


sqlcmd.exe  -S servername -E   -d  DatabaseName  -i SQLQuery1.sql>>output.txt
sqlcmd.exe  -S servername -E   -d  DatabaseName  -i SQLQuery2.sql>>output.txt


pause