SQL Server : Nested Query File

Question

How would I run a query inside another query in SQL Server?

NOTE: what I mean by query is the my_query_name.sql created in SSMSSLN project solution. So I want to run my_query_name_1.sql inside my_query_name_2.sql.

Any idea ?

Possible duplicate of [TransactSQL to run another TransactSQL script](http://stackoverflow.com/questions/5237198/transactsql-to-run-another-transactsql-script) — PM 77-1, Apr 15 '16 at 21:36
@PM77-1 It's not working. Dunno why ? I tried putting the :r /script Path/ in my .sql fil but it underlined by sql. Not recognized as a command — Kurt Miller, Apr 15 '16 at 21:49

Spock · Answer 1 · 2016-04-15T22:48:22.837

Here's something to think about... It uses the BULK INSERT function to load the SQL statements into a temp table, and then uses dynamic SQL to execute them.

And yes... all the usual warning of SQL injection apply, etc, etc,

--Assume this is your main sql file.....
--Execute some SQL commands....

--Now load the second SQL file and execute it

DECLARE @SQL        NVARCHAR(MAX);    
CREATE TABLE #SQL (value    nvarchar(max));

BULK INSERT #SQL
FROM 'C:\TEMP\select2.sql' 
WITH 
(
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '|',    --<Make sure these characters
    ROWTERMINATOR = '|'       --<don't appear in your SQL statements
);

SELECT @SQL = Value FROM #SQL
EXEC (@SQL);
TRUNCATE TABLE #SQL;

-- Do some other sql commands in your main query


--Now load the third SQL file and execute it
BULK INSERT #SQL
FROM 'C:\TEMP\select3.sql'
WITH 
(
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '|',    --<Make sure these characters
    ROWTERMINATOR = '|'       --<don't appear in your SQL statements
);

SELECT @SQL = Value FROM #SQL
EXEC (@SQL);

--Do some more sql commands
--etc...

--Clean up the temp table
DROP TABLE #SQL

This approach will work without SSMS, and can be used in any environment

I don't understand. You want to run a query from another query...? This shows you how to load a query from a file... assume my answer is your 'my_query_name_1.sql', and it's loading my_query_name_2.sql, and my_query_name_3.sql, etc.. — Spock, Apr 15 '16 at 22:12
Ok. Tried to run the script (I'll need to study it obviously). I got a "There is already an object named '#SQL' in the database." Where is the #SQL located ? — Kurt Miller, Apr 15 '16 at 22:39
#SQL is a temp table that I use... you can add DROP TABLE #SQL to the bottom of your query. That will clean up the temp table. I'll update my answer — Spock, Apr 15 '16 at 22:47

score 0 · Answer 2 · answered Apr 15 '16 at 22:48

In SQL Server Management Studio first you will need to enable xp_cmdshell command.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Then execute the command below

EXEC sys.xp_cmdshell 'sqlcmd -S (local) -d Experiments -i C:\Hello.sql'

Here, Server = (local), Database = Experiments and SQL File = C:\Hello.sql

This would successfully execute query inside a SQL file, but the output format is different than the normal.

SQL Server : Nested Query File

2 Answers2