batch file for executing SQL scripts - no carriage returns

Question

There are some script files in a folder that must be executed on a SQL Server database. Not all of the files in the folder, just a subset. The following command is in a batch file and it is working but the files are all on one line. It is very difficult to read (there are more files than the 3 shown in the example). Is there a way that the files can be listed on individual lines (for readability) and not lose the functionality? The goal is to perform this task using a batch file only - not a batch file + a SQL file that uses the :r command for each script to be executed.

sqlcmd -S ServerInstance -E -d DatabaseName -i .\Tables\stage.products.sql, .\Tables\dbo.products.sql, .\Sprocs\dbo.BuildProducts.sql

Could this help? http://stackoverflow.com/questions/2583517/run-all-sql-files-in-a-directory — cdev, Apr 17 '14 at 21:51
That link appears to execute all files that end with .sql. I have to explicitly state the file names so it does NOT run all of the files ending in .sql in the folder. — knot22, Apr 17 '14 at 22:15

score 2 · Answer 1 · answered Apr 17 '14 at 22:35

2

If it's a batch file you can add a ^ to the end of each line: echo Line 1 ^ and line 2 ^ and line 3 will all echo in a single line.

When you run the above batch file the results are:

Line 1 and line 2 and line 3 will all echo in a single line.

answered Apr 17 '14 at 22:35

cdev

166
5

+1, but be careful. There is a limit to the total number of characters that can appear on a command line. All the lines that use line continuation will be considered as one line. For Windows Vista and beyond the limit is 8191 characters. – dbenham Apr 18 '14 at 00:57

score 1 · Accepted Answer · answered Apr 18 '14 at 01:20

@echo off
setlocal EnableDelayedExpansion

set "sqls="
for %%a in (
            .\Tables\stage.products.sql
            .\Tables\dbo.products.sql
            .\Sprocs\dbo.BuildProducts.sql
           ) do set "sqls=!sqls!, %%a"

sqlcmd -S ServerInstance -E -d DatabaseName -i %sqls:~1%

Previous Batch file allows you to list files on individual lines for readability as you requested. It does NOT allows you to include more files than originally you could include in one line.

batch file for executing SQL scripts - no carriage returns

2 Answers2