Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Can anyone help me write a script to run all of the SQL files in a particular folder?

I need to deploy a bunch of Stored Procedures and I want to deploy them all at once without having to manually run each one. We run Microsoft SQL Server 2005.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Windows command line processor support the for statement, so you can create a .cmd file with something like that:

for %%f in (*.sql) do osql -U<User> -P<Pass> -S<Server> -d<Database> -i%%f

This will process all .sql files of the current folder sequentially (you can use also sqlcmd as suggested in another answer).

Reference: Microsoft Windows XP - For

Also, you can specify the database directly in the script using at the beginning:

use DatabaseName
go

The go is important because some statements like CREATE VIEW or CREATE PROCEDURE needs to be at the beginning of a batch of SQL commands.

share|improve this answer
    
I'm running Windows 7, I assume they didn't take away the For loop, but you never know. Do you know if this works on 7? –  EJC Nov 4 '10 at 14:01
1  
The Windows team of Microsoft take compatibility of batch files very seriuously, so they don't change almost anything from the batch interpreter or they'll break existing batch files (which are used a lot in some corporate environments), see blogs.msdn.com/b/oldnewthing/archive/2005/09/09/462906.aspx. –  Alberto Martinez Nov 4 '10 at 21:29

If you need to run them in a particular order then the best solution is just to write a bat file:

run fileA.sql
run fileB.sql
run fileZ.sql
run fileM.sql
...
share|improve this answer
    
No order. So I can just use run C:\folder\*.sql how does it know what DB to run them on? –  EJC Nov 3 '10 at 14:29
    
@EJC - you can specify the database in the script - I don't have an example immediately to hand. –  ChrisF Nov 3 '10 at 14:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.