Nest a SQL file into another in SQL Server

Question

I have a simple SQL file I'm using to perform some test:

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.

Poseidon · Accepted Answer · 2023-06-07T20:09:15.987

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 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;

score 3 · Answer 2 · answered Jun 30 '22 at 09:50

In general, reading a .sql file from the hard drive inside another .sql file is an anti-pattern. Instead, it's better to create a stored procedure:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

After that, you can execute it in an arbitrary place in sql when like this:

EXEC procedure_name;

--

If you still need it, then it's more correct to do it like this:

DECLARE @SQL varchar(MAX)
SELECT @SQL = BulkColumn
FROM OPENROWSET
    ( BULK 'c:\temp\cleanup.sql'
    , SINGLE_BLOB ) AS MYTABLE
--PRINT @sql
EXEC (@sql)

--

Note

Microsoft does not recommend using the xp_cmdshell procedure. This is not safe.

Nest a SQL file into another in SQL Server

2 Answers2

Note