How can I execute a set of .SQL files from within SSMS?

Question

How could I execute a set of .SQL files (each does some data transformations) from within SQL Server Management Studio?

What other alternative are there for executing .SQL files in batch?

Solution 1

While SQLCMD.exe is the best way, SSMS also has a SQLCMD mode where you can execute a SQLCMD script. To enable this mode click Query in menu bar then select SQLCMD Mode.

The ":r filename.sql" command is the SQLCMD script command to import and execute a sql script file. You know you are in SQLCMD mode because any lines that are SQLCMD script commands will appear with colored (gray I think) background.

:setvar path "c:\Path_to_scripts\"
:r $(path)\file1.sql
:r $(path)\file2.sql

Solution 2

Use SqlCmd.exe.

For example:

sqlcmd -S myServer\instanceName -i C:\myScript.sql

or to save output to a file:

sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

Comments

[AjV Jsy]

Notes - file paths are relative to the machine running SSMS, not the server. To get used to this feature it's easier to add the SQLCMD button to the toolbar, and then you can see the mode enabled/disabled on the button while a : command's back colour changes grey/white in the query window.

[what evAR]

Why is sqlcmd better than the solution suggested in this post?

[RonC]

I could never get the sqlcmd.exe approach to work. I'm sure it was just because I was specifying one of the parameters incorrectly with regard to the server, username or password. But I was able to use this answer and use SQLCMD mode in SSMS without any issue. Perhaps because I was able to first connect to the database as I always do in SSMS. Excellent approach.

[revobtz]

Very simple and useful. Note: If you have a huge SQL file this is the way to go! I imported 5 gb of script and couldn't do it from management studio. This was the only way to do it.