title: How to include an sql file within another sql file in MS SQL?
body: <p>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.</p>
  
  <p>Currently I need to open each script in Visual Studio and execute them in the correct order by clicking <code>Execute</code> (ctrl shift E).</p>
  
  <p>Is there a way to create a script like <code>Master.sql</code> that contains includes like this:</p>
  
  <pre><code>BEGIN TRANSACTION
  
  ImportOrInclude myDB1.sql
  ImportOrInclude myDB2.sql
  ...
  
  COMMIT
  </code></pre>
  
  <p>This is only required to run from Visual Studio and will not be part of the application itself.</p>
  
  <p>How can this be done?</p>
  
  <p><strong>EDIT 1</strong></p>
  
  <p>I've found that there is something called SQLCMD Scripts which can import SQL files:
  <a href="http://msdn.microsoft.com/en-us/library/aa833281%28v=vs.80%29.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/aa833281%28v=vs.80%29.aspx</a></p>
  
  <p>But my question is then how to get the directory path of the current solution  into the <strong>:r</strong> command.</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>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:</p>
  
  <pre><code>-- turn on in menu: Data -&gt; Transact SQL editor -&gt; 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
  </code></pre>