How to Run a SQL Script in Tsql

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

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

How to run a sql file in a query in SQL Server

Use SSMS in SQLCMD mode to run an external SQL file:

:R Pathtoyourfileinthesqlserver

https://msdn.microsoft.com/en-us/library/ms174187.aspx

How to run a SQL script in tsql

I really didn't find how to do this and I'm starting to thinks it just can't be done in tsql. However I solved for my specific problem redirecting the stdin and stdout. I use a bash script like this:

tsql connection parameters < tsql_input.sql > tsql_output.csv
lines=`cat tsql_output.csv | wc -l`

# The output includes the header info of tsql and the "(n number of rows)" message
# so I use a combination of head and tail to select the lines I need
headvar=$((lines-2))
tailvar=$((lines-8))

head -$headvar tsql_output.csv | tail -$tailvar tsql_output.csv > new_output_file.csv

And that saves just the query result on 'new_output_file.csv'

Run SQL script via command line to remote server

-d - Specifies the database name - learn.microsoft.com/en-us/sql/tools/sqlcmd-utility – Ctznkane525

sqlcmd -S myServer\instanceName -i C:\myScript.sql -d DatabaseName

How to schedule to run SQL script on SQL Server?

If you don't want to change the existing settings, use the Windows Task Scheduler with SQLCMD..

Have a look here https://learn.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-2017

1. Open a command prompt window.

2. In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

3. Press ENTER.

You can use the same solution as here: create Scheduler task to invoke SQLCMD

Your best bet would be to create a Console Application which executes
the SQL Command and performs the email.

You can then use setup a task under Task Scheduler to use the Start a
program option and run it on the schedule you prefer

EDIT:
To get rid of the dottet line have a look here:

Sqlcmd to generate file without dashed line under header, without row count

To remove column headers you should use -h parameter with value -1.
See ref (section Formatting Options).

Or from this post:

How to export data as CSV format from SQL Server using sqlcmd?

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable"
-o "MyData.csv" -h-1 -s"," -w 700
-h-1 removes column name headers from the result

-s"," sets the column seperator to ,

-w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)

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