Translate

Sunday, April 24, 2011

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







11 comments:

  1. Hi, where do i run this bat file. In the server?

    ReplyDelete
  2. Hi,
    Place the bat file in the same folder as where your sql scripts reside. All the bat file does, is that it runs all the sql scripts in the folder that the bat file resides in.

    Let me know if that helps.

    ReplyDelete
  3. Also I added an image in the article, see if that helps :)

    ReplyDelete
  4. Thanks! this is helpful

    ReplyDelete
  5. Glad I could be of some help :)

    ReplyDelete
  6. This is so brilliant and simple it makes me want to cry. Well maybe not cry, but thank you its really cool!

    ReplyDelete
  7. Thank you for the post, This is really nice! but is there a way to order it in ascending numbers? Because now i have scripts 1.sql,2.sql...10.sql,11.sql... and if i do it like this it will execute 10.sql before 2.sql

    Otherwise I guess I will just have to rename my 2000+ sql-files

    ReplyDelete
    Replies
    1. Thank you for your question. I think there should be a way to convert the file name to and integer and then go through that list in an ascending order. However I myself have never done that before. One thing you could do is write a simple C# program to rename all file names to 4 characters. Then 2.sql would become 0002.sql. Doing that would be a lot easier and faster than manually renaming 2000 of them!

      Delete
  8. Is there any way i can add more details printed to the output.txt file such as the script file names that are being run

    ReplyDelete
    Replies
    1. Add this as the first line inside your loop

      echo Executing %%f>>output.txt

      Delete

Comments will appear once they have been approved by the moderator