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.

Answer 1

You can perform SQLCMD from inside T-SQL/SSMS. See the answers here: How can I execute a set of .SQL files from within SSMS?

You need to set the query window to SQLCMD mode. That is a requirement, it is the client tool that does the work for you, not the database engine. To enable this mode click Query in menu bar then select SQLCMD Mode.

For a pure T-SQL solution, I've just tested the code below and it works 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 disables xp_cmdshell after execution, as Microsoft recommend.

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;

Answer 2

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.