CODEWITHSUNDEEP
sql-servervisual-studiot-sql
Jiří Herník
Jiří Herník

Reputation: 2477

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

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

Upvotes: 19

Views: 28597

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

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

Upvotes: 17

Ocaso Protal
Ocaso Protal

Reputation: 20247

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.

Upvotes: 2

Related Questions