As mentioned in my previous posts, you can use oSql or sqlCmd to execute SQL against a database. In my case I often have hundreds of these files that I wish to run (a bunch of stored procs into the DB) – and it turns out that if you want to do this it will connect to the DB, run the script, close the connection. Repeat.
All this adds up after a while and now running in all of my stored procs can take 10 minutes.
So instead I wrote my own c# application to connect to the DB, find any .sql files and run them all in. It’s 1000 times faster without all the overheads of establishing the connection each time.
The only gotcha is the “GO” command! Often you find yourself writing SQL like this:
UPDATE TABLE1 ADD NewColumn int
GO
update Table1 set NewColumn = OldColumn+1
but SQL Server doesn’t actually understand the GO command. The GO command merely separates “batches” of SQL. So my lovely little c# app has to look for these GO commands and execute the code one block at a time. Easily enough done and it now works magically. If anyone wants the code let me know… but really, you could write it yourself in half an hour.