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

sql-server batch-file ssms
Even Mien picture Even Mien · May 8, 2009 · Viewed 53.8k times · Source

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?

Answer

DCNYAM picture DCNYAM · May 8, 2009

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

Related questions

Execute SQL script from command line

I need to alter a database using a batch file, for a simple example, drop a table. I´m using local SQL Express (SQL Server 2008 R2) with user sa and its password. How would the bat file be? How can …

Read More
How to run multiple SQL scripts using a batch file?

I have a case where i have got 10+ SQL script. I don't want to go and run all my scripts 1 by 1. Is there a way that i can run all my scripts in succession in SQL Management studio. I found …

Read More
Accessing the returned value of a SQL statement in SQLCMD

I'm trying to get the value of a SQL statement when I run it in a DOS batch file ... sqlcmd -E -S DEVSERVER -Q "SELECT COUNT(1) as [CaseCount] FROM Cases" I'm not after the error level as in this stackoverflow …

Read More