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

Question

How can I execute a set of .SQL files (each of which performs some data conversion) from SQL Server Management Studio?

What is another alternative for executing .SQL files in batch mode?

Answer 1

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

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

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

Answer 2

Use SqlCmd.exe .

For example :

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

or save the output to a file:

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