How to create a batch file using SQL files from a folder?

Problem: How to create a batch file using SQL files from a folder?

Solution: Sometimes a need is generated where we need to create batch file to include scripts that needs to be run on a server and pointed to a database. I broke it down into further things below:

Step 1:

xp_cmdshell

for executing this extended stored procedure this requires configuration of server to enable this feature first and the user to execute this stored procedure must have sysadmin permissions. If the user doesn’t have sys admin permission sp_xp_cmdshell_proxy_account to get the privilege.

Step 2:

A batch file is generally an executable which can be run from the command prompt. Batch file can run a file when sqlcmd can be used. To know more about sqlcmd please go through this article. sqlcmd execution from CMD prompt

Step 3:

To Export a file from SQL server BCP command is specified, please read this article export a file from SQL

Example:

Example 1: In this example, I would like to show you how we can search for all SQL files with in a directory. Create an out file using bcp commands to export the query result into out files.

As a part of this I would like to change the server configurations using sp_configure.

--trying to see if there are any unsaved options

EXEC sp_configure 'show advanced options',       1

GO

RECONFIGURE

GO

-- enabling xp_cmdshell

EXEC sp_configure 'xp_cmdshell',             1

GO

RECONFIGURE

GO

USE Master
GO

--check wether table exits

IF NOT EXISTS (

                                SELECT 1

                                FROM Information_schema.tables

                                WHERE table_schema = 'dbo'

                                                AND table_name = N'batchfilequery'

                                                AND table_type = 'BASE TABLE'

                                )

BEGIN

CREATE table dbo.batchfilequery (scripts varchar(2000))

END

DECLARE @sqlcommand VARCHAR(8000),

@filename VARCHAR(1000) = 'RunScripts.bat', -- output batch filename

@SourceLocation VARCHAR(1000)= 'C:\Temp\' -- source folder path

Declare @batchscripts table  (subirectory varchar(1000))

--Generate data in the dummy file

                SET @sqlcommand = 'exec master..xp_cmdshell ''dir "' +@SourceLocation + '*.sql" /b'''

Insert into @batchscripts(subirectory)

EXEC (@sqlcommand)

TRUNCATE TABLE  dbo.batchfilequery

INSERT INTO dbo.batchfilequery

SELECT 'SetLocal EnableDelayedExpansion

set HOST=%1

set DB=%2

ECHO ...   Deleting existing .out files

DEL  .\*.out'

UNION

--creating sqlcmd statement for each script found from the source filename

select 'sqlcmd -S %HOST% -d %DB% -E -n -w 500 -i '+subirectory+' -o '+SUBSTRING(subirectory,0,charindex('.sql',subirectory))+'.out' from @batchscripts where subirectory like '%.sql'

                --Generate batch file output

                SET @sqlcommand = 'exec master..xp_cmdshell ''bcp " SELECT * FROM dbo.batchfilequery " queryout "' + @SourceLocation+@filename + '"             -T                           -c                           -t,'''

                EXEC (@sqlcommand)

               

                IF exists(SELECT 1

                                FROM Information_schema.tables

                                WHERE table_schema = 'dbo'

                                                AND table_name = N'batchfilequery'

                                                AND table_type = 'BASE TABLE')

                BEGIN

                DROP TABLE dbo.batchfilequery

                END

OUTPUT: