Transactsql to Run Another Transactsql Script

TransactSQL to run another TransactSQL script

Try this if you are trying to execute a .sql file in SSMS:

:r C:\Scripts\Script1.sql
:r C:\Scripts\Script2.sql
:r C:\Scripts\Script3.sql
...

note: for this to run turn on sql command mode (Query > SQLCMD Mode)

If these are scripts you run fairly often you might consider dropping them in a stored proc and running them that way...

You can also do it through sqlcmd (which I believe is more common):

sqlcmd -S serverName\instanceName -i C:\Scripts\Script1.sql

Is it possible to reference other scripts in a TSQL script file?

If you are running them with sqlcmd you can use r: to include other scripts

:r C:\Files\selectFromTable.sql 

Editing SQLCMD Scripts in the Transact-SQL Editor

Best way to run separate parts of a single T-SQL script together at once

All the other answers are fine if you can/want to make functional modifications to your script, but if you would rather avoid that, as I suspect, the way I usually do what you are talking about is by block-commenting the parts I want to skip:

So I just change this:

DECLARE @variables;

Some Code I want to skip this time;

Some Code I want to execute this time;

Maybe some more code I want to skip this time;

To this:

DECLARE @variables;
/*
Some Code I want to skip this time;
*/
Some Code I want to execute this time;
/*
Maybe some more code I want to skip this time;
*/

What is the T-SQL syntax to connect to another SQL Server?

Also, make sure when you write the query involving the linked server, you include brackets like this:

SELECT * FROM [LinkedServer].[RemoteDatabase].[User].[Table]

I've found that at least on 2000/2005 the [] brackets are necessary, at least around the server name.

What is the T-SQL syntax to connect to another SQL Server?

Also, make sure when you write the query involving the linked server, you include brackets like this:

SELECT * FROM [LinkedServer].[RemoteDatabase].[User].[Table]

I've found that at least on 2000/2005 the [] brackets are necessary, at least around the server name.

How to run sql script using SQL Server Management Studio?

This website has a concise tutorial on how to use SQL Server Management Studio. As you will see you can open a "Query Window", paste your script and run it. It does not allow you to execute scripts by using the file path. However, you can do this easily by using the command line (cmd.exe):

sqlcmd -S .\SQLExpress -i SqlScript.sql

Where SqlScript.sql is the script file name located at the current directory. See this Microsoft page for more examples

Generate Scripts equivalent for Transact SQL

Your development deliverable should not be a database binary (the .MDF file), but a deployment script. You treat your database deployment and upgrade just like any other source file, place it under source control, have peer code reviews at check in etc etc. Modifying directly the .MDF and then reverse engineering to deploy it is just plain bad. The problem you encountered now is just one of the problems, and there are many more problems, specially around the issue schema changes done during an application version upgrade. See Version Control and your Database.

Now is true that the entire VS tool set is trying to guide you down the path of 'just edit your MDF in the VS Database Explorer and everything will be fine'. Nothing will be fine and one or more deployment meltdowns are just ahead in your life, but lets pretend that VS does a good thing.

You can automate the extraction of the current schema and deployment of it via 3rd party commercial tools like Red Gate's SQL Compare, or you can roll your own 'Generate Scripts' fairly easy. SSMS all it does it invokes the SMO scripting capabilities to script out an entire database. You can do the same: instantiate a Scripter object instance, then add to it the objects you want scripted, then extract the T-SQL generated script. That is exactly what 'Generate Scripts' in SSMS does. There is an example in MSDN for scripting:

   //Connect to the local, default instance of SQL Server. 
  Server srv = new Server(); 

   //Reference the AdventureWorks2008R2 database.  
  Database db = srv.Databases["AdventureWorks2008R2"]; 

   //Define a Scripter object and set the required scripting options. 
  Scripter scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 

   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] smoObjects = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[1] ;
   foreach (Table tb in db.Tables) {   
      smoObjects[0] = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         System.Collections.Specialized.StringCollection sc;
         sc = scrp.Script(smoObjects); 
         foreach ( string st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 

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

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 Topics