Introduction

Automating database tasks can significantly enhance productivity, especially when dealing with multiple SQL scripts. In this tutorial, we will create a batch script to execute SQL scripts from a specified folder and log the results into a single CSV file, including the name of each SQL script in the log.

Prerequisites

• A Windows environment
• SQL Server and sqlcmd utility installed
• Basic knowledge of batch scripting

Step-by-Step Guide

Step 1: Setting Up the Batch Script

Create a batch script file (run_sql_scripts.bat) with the following content. This script iterates through all SQL files in a specified folder, runs them using sqlcmd, and logs the results to _inside.csv, including the script name.

@ECHO off

REM Folder where the queries are located
set "specific_folder=U:\DataManagement\CreditCardMigration\jchemile\esl-tests\.TESTS\Encryption"

cls

REM Enable delayed expansion for variables inside the loop
setlocal enabledelayedexpansion

for /R "%specific_folder%" %%F in (*.sql) do (
    REM Get the full path of the SQL file
    set "filepath=%%F"
    
    REM Get the name of the file without the extension
    set "filename=%%~nxF"

    REM Run the SQL script and append the filename and output to _inside.csv
    ECHO !filename!, >> "_inside.csv"
    sqlcmd /S dwsqldev5 /d STG -E -i"%%F" -W -s, -h -1 >> "_inside.csv"
)

REM End delayed expansion
endlocal

exit
@ECHO off

REM Folder where the queries are located
set "specific_folder=U:\DataManagement\CreditCardMigration\jchemile\esl-tests\.TESTS\Encryption"

cls

REM Enable delayed expansion for variables inside the loop
setlocal enabledelayedexpansion

for /R "%specific_folder%" %%F in (*.sql) do (
    REM Get the full path of the SQL file
    set "filepath=%%F"
    
    REM Get the name of the file without the extension
    set "filename=%%~nxF"

    REM Run the SQL script and append the filename and output to _inside.csv
    ECHO !filename!, >> "_inside.csv"
    sqlcmd /S dwsqldev5 /d STG -E -i"%%F" -W -s, -h -1 >> "_inside.csv"
)

REM End delayed expansion
endlocal

exit

Explanation

• @ECHO off: Disables command echoing for cleaner output.
• set "specific_folder=...": Sets the folder containing your SQL scripts.
• cls: Clears the screen.
• setlocal enabledelayedexpansion: Enables delayed variable expansion to use variables inside the loop.
• for /R "%specific_folder%" %%F in (*.sql) do: Loops through all .sql files in the specified folder and subfolders.
• set "filepath=%%F": Stores the full path of the current SQL file.
• set "filename=%%~nxF": Extracts the file name with extension.
• ECHO !filename!, >> "_inside.csv": Appends the file name to the CSV file.
• sqlcmd /S dwsqldev5 /d STG -E -i"%%F" -W -s, -h -1 >> "_inside.csv": Runs the SQL script and appends the output to the CSV file.

Step 2: Running the Script

To run the script:

1. Save the batch script (run_sql_scripts.bat) to your desired location.
2. Open Command Prompt.
3. Navigate to the location of the script using the cd command.
4. Execute the script by typing run_sql_scripts.bat and pressing Enter.

Step 3: Reviewing the Output

The _inside.csv file will contain the results of each SQL script, with each line starting with the script name followed by the output. Here’s an example of what the file might look like:

query1.sql,Result 1
query2.sql,Result 2
query3.sql,Result 3

Each row includes the script name, followed by the result of the SQL command.

Conclusion

By using this batch script, you can automate the execution of multiple SQL scripts and log their output efficiently. This approach ensures that you have a clear record of which script produced each result, which is essential for debugging and auditing purposes.