patternsqlMinor
Nest a SQL file into another in SQL Server
Viewed 0 times
filesqlnestintoanotherserver
Problem
I have a simple SQL file I'm using to perform some test:
Before launching all that, I would like to do some cleaning:
For readability reasons, I'd like to put all the cleaning commands in a file "cleanup.sql" and launch it at the beginning of my SQL-file. How do I do something like that?
I guess it's something like:
... but I'm not sure about the syntax.
DECLARE @VAR1 AS VARCHAR(100) = '12345'
DECLARE @VAR2 AS INTEGER = 54321
WAITFOR DELAY '00:00:02'
INSERT Orders SELECT @VAR1 AS COL1, @VAR2 AS COL2, ...
...Before launching all that, I would like to do some cleaning:
/* Clean SpecialSubSubEntries */
DELETE SpecialSubSubEntries
WHERE Id IN (SELECT Id FROM SubSubEntries
WHERE SubEntryId IN (SELECT Id FROM SubEntries
WHERE EntryId IN (SELECT Id FROM Entries
WHERE Name='ENTRY01')
)
)
DELETE SubSubEntries
WHERE SubEntryId IN (SELECT Id FROM SubEntries
WHERE EntryId IN (SELECT Id FROM Entries
WHERE Name='ENTRY01')
)
...For readability reasons, I'd like to put all the cleaning commands in a file "cleanup.sql" and launch it at the beginning of my SQL-file. How do I do something like that?
I guess it's something like:
EXECUTE ".\cleanup.sql"... but I'm not sure about the syntax.
Solution
You can execute scripts in SQLCMD mode from within SSMS. See the answers here: How can I execute a set of .SQL files from within SSMS?
You will need to set your SSMS query to SQLCMD mode in order to do that, it is the client tool that does translates that code, not the database engine.
To enable this mode, drop down
I provided a pure T-SQL solution without any hassle, It was tested and working like a charm:
An important note: it's not recommended to use this approach unless you are very aware of the risks behind it. The script enables then disables
If xp_cmdshell must be used, as a security best practice it is recommended to only enable it for the duration of the actual task that requires it.
You will need to set your SSMS query to SQLCMD mode in order to do that, it is the client tool that does translates that code, not the database engine.
To enable this mode, drop down
Query menu in SSMS then select SQLCMD Mode.I provided a pure T-SQL solution without any hassle, It was tested and working like a charm:
An important note: it's not recommended to use this approach unless you are very aware of the risks behind it. The script enables then disables
xp_cmdshell after the execution of the scripts, as Microsoft recommends here.If xp_cmdshell must be used, as a security best practice it is recommended to only enable it for the duration of the actual task that requires it.
exec sp_configure 'show advanced options',1;reconfigure with override;
exec sp_configure 'xp_cmdshell',1;reconfigure with override;
declare @sqlfile nvarchar(100),@sqlcmd varchar(4000)
exec xp_cmdshell 'del c:\SQLscript.sql && echo SELECT ''This Is a script running on SQLCMD from SSMS'' DataColumn >> c:\SQLscript.sql',no_output --test script
set @sqlfile = 'c:\SQLscript.sql' --script location
set @sqlcmd = 'sqlcmd -E -i '+@sqlfile
/* the above string runs sqlcmd on the server which sqlcmd was invoked, and with trusted connection,
if you need to run the script on a different server/instance, you'll need replace the "-E" with "-S" and specify servername/ip address and credentials with "-U" for username and "-P" for password*/
exec xp_cmdshell @sqlcmd --executing script
exec sp_configure 'xp_cmdshell',0;reconfigure with override;
exec sp_configure 'show advanced options',0;reconfigure with override;Code Snippets
exec sp_configure 'show advanced options',1;reconfigure with override;
exec sp_configure 'xp_cmdshell',1;reconfigure with override;
declare @sqlfile nvarchar(100),@sqlcmd varchar(4000)
exec xp_cmdshell 'del c:\SQLscript.sql && echo SELECT ''This Is a script running on SQLCMD from SSMS'' DataColumn >> c:\SQLscript.sql',no_output --test script
set @sqlfile = 'c:\SQLscript.sql' --script location
set @sqlcmd = 'sqlcmd -E -i '+@sqlfile
/* the above string runs sqlcmd on the server which sqlcmd was invoked, and with trusted connection,
if you need to run the script on a different server/instance, you'll need replace the "-E" with "-S" and specify servername/ip address and credentials with "-U" for username and "-P" for password*/
exec xp_cmdshell @sqlcmd --executing script
exec sp_configure 'xp_cmdshell',0;reconfigure with override;
exec sp_configure 'show advanced options',0;reconfigure with override;Context
StackExchange Database Administrators Q#313931, answer score: 5
Revisions (0)
No revisions yet.