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

Nikola Markovinović · Accepted Answer · 2013-06-04T20:38:52.700

17

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

edited Jun 04 '13 at 20:38

answered Mar 07 '12 at 10:18

Nikola Markovinović

18,963
5
46
51

Sure it does, i've just tested it. – Nikola Markovinović Mar 07 '12 at 10:45
I tried to use sql command included in visual studio, and it works in different way – Jiří Herník Mar 07 '12 at 10:52
Now it's working ok ;) But missing the $(SolutionDir) variable so much. – Jiří Herník Mar 07 '12 at 11:27

score 2 · Answer 2 · answered Mar 07 '12 at 10:20

Use the Business Intelligence Development Studio that comes with SQL Server to do this. Create a new SSIS Package. Inside this package create several Execute SQL Tasks in the Control Flow for each file you have and set SQLSourceType to FileConnection and choose your file.

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

2 Answers2