sql server: how to execute a .sql from another query?

Question

I have a .sql script with a lot of action queries that work on some staging tables. This script needs to be run twice with some other commands in-between i.e.:

Load the staging table from source A
Use do_stuff.sql to process it
Move the results somewhere.
Repeat Steps 1-3 for source B.

The brute force approach would be to just copy & paste dostuff.sql as needed. While this would technically work, is there a better way?

I'm hoping there's a command like RunThisSQL 'C:\do_stuff.sql' that I haven't discovered yet.

Update

Well, it's been about 5 years and I just re-discovered this old question. I did this recently and made a cursor to loop thru a master table. For each record in that master table, the script runs through an inner script using variables set by the master table.

https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

How are you executing the script? One method would be to write a batch/PowerShell routine which runs sqlcmd with your script, something like sqlcmd -SMyServer -ido_stuff.sql — voithos
– voithos, Commented May 21, 2013 at 18:31
@voithos, I'm running this manually from within MS SQL Server Management Studio. In other words, open the .sql file and hit Execute. — PowerUser
– PowerUser, Commented May 21, 2013 at 18:39
Try this cmd @'C:/do_stuff.sql'; in sql command promte. This works in Oracle. — VKPRO
– VKPRO, Commented May 21, 2013 at 18:48
The problem is that the SQL code, when it's actually running, is running on the server. It no longer has any (real) relationship with the SSMS instance that sent the query to the server, other than the connection. And there's nothing build into TDS to allow it to somehow request a new file to be loaded and sent over that connection. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 22, 2013 at 6:47

Yaugen Vlasau · Accepted Answer · 2013-05-21 18:44:50Z

If you use visual studio you can create "Sql Server Database" project. Withing the project you can create script that let you execute your *.sql files in a manner

 /*
Post-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.       
 Use SQLCMD syntax to include a file in the post-deployment script.         
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the post-deployment script.       
 Example:      :setvar TableName MyTable                            
               SELECT * FROM [$(TableName)]                 
--------------------------------------------------------------------------------------
*/

see also. http://candordeveloper.com/2013/01/08/creating-a-sql-server-database-project-in-visual-studio-2012/

Jeotics · Accepted Answer · 2013-05-22 06:41:30Z

2

Try using xp_cmdshell.

 EXEC xp_cmdshell  'sqlcmd -S ' + @ServerName + ' -d ' + @DBName + ' -i ' +@FileName

answered May 22, 2013 at 6:41

Jeotics

512 bronze badges

Comments

mrduncle1 · Accepted Answer · 2018-08-31 03:51:54Z

xp_cmdshell and concatenation do not play together nicely, often resulting in an "Incorrect syntax near '+'" error. So further to Jeotics solution above you will need to make a variable of the entire string you pass to xp_cmdshell (including quotes around anything that may contain a space (eg filepath\filename). This is mentioned in the Microsoft documentation for xp_cmdshell here. Other issues you will have to contend with are the default set up for SQL Server which has xp_cmdshell disabled as outlined here and granting permission to non-system administrators to use xp_cmdshell outlined here. The documentation generally advises against giving xp_cmdshell rights to too many people owing to it being a vehicle for those with malintent but if, like me, you have minimal and trustworthy database users then it seems like a reasonable solution. One last issue that requires correct configuration is the SQL Server Agent as outlined here. Documentation outlines that SQL Agent is responsible for background scheduling (such as back ups) and performance of command line statements, etc..

DECLARE
    @Server nvarchar (50)
    ,@Database nvarchar(50)
    ,@File nvarchar(100)
    ,@cmd nvarchar(300);

SET @Server = server_name;
SET @Database = database_name;
SET @File = 'C:\your file path with spaces';
SET @cmd = 'sqlcmd -S ' + @Server + ' -d ' + @Database + ' i "' + @File + '"';

EXEC xp_cmdshell @cmd;

Venkataraman R · Accepted Answer · 2018-08-31 04:31:23Z

1

There are some security issues with enabling xp_cmdshell in SQL Server. You can create a CLR Stored procedure, which executes the passed file content. This CLR stored procedure is especially for this purpose, not like xp_cmdshell, which can do anything over the command prompt.

issues with enabling xp_cmdshell

Create CLR stored procedure

answered Aug 31, 2018 at 4:31

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Collectives™ on Stack Overflow

sql server: how to execute a .sql from another query?

Update

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related