How to include an sql file within another sql file in MS SQL?

Question

We have several SQL scripts which are generated from an Entity Model. They need to be run in a specific order. Additionally there are several filling scripts which insert test data into the database.

Currently I need to open each script in Visual Studio and execute them in the correct order by clicking Execute (ctrl shift E).

Is there a way to create a script like Master.sql that contains includes like this:

BEGIN TRANSACTION

ImportOrInclude myDB1.sql
ImportOrInclude myDB2.sql
...

COMMIT

This is only required to run from Visual Studio and will not be part of the application itself.

How can this be done?

EDIT 1

I've found that there is something called SQLCMD Scripts which can import SQL files: http://msdn.microsoft.com/en-us/library/aa833281%28v=vs.80%29.aspx

But my question is then how to get the directory path of the current solution into the :r command.

EDIT 2

So I figured out how to do it, not perfectly, but it works. The downside is that the $(SolutionDir) is not loaded from the Visual Studio variables, so you need to set it up manually. This code is meant to be run in Visual Studio:

-- turn on in menu: Data -> Transact SQL editor -> SQL CMD mode
-- set this to path where the .sln file is.
:setvar SolutionDir C:\_work\projectname\

:!! echo $(SolutionDir)Maa.EntityModel.All\DbWEntityModel.edmx.sql 
:r $(SolutionDir)Maa.EntityModel.All\DbWEntityModel.edmx.sql 
go

:!! echo $(SolutionDir)Maa.EntityModel.All\DblQEntityModel.edmx.sql 
:r $(SolutionDir)Maa.EntityModel.All\DbQEntityModel.edmx.sql 
go

Answer 1

Use sqlcmd utility.

Extract you might find interesting:

-i input_file[,input_file2...]

Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order. Do not use any spaces between file names. sqlcmd will first check to see whether all the specified files exist. If one or more files do not exist, sqlcmd will exit.

Example:

sqlcmd -dDataBaseName -E -Stcp:ServerName\instancename -imaster.sql

Master.Sql:

:r file1.sql
:r file2.sql