Bat + sqlcmd run scripts in batches and batsqlcmd run scripts

Bat + sqlcmd run scripts in batches and batsqlcmd run scripts

Hello, this BAT script helps developers to Batch Execute all SQL scripts in a folder by file name in the specified database. Powershell invoke-sqlcmd is too cumbersome to handle.

Bat file

@echo off@REM ******** ******** General Batch for Starting SQL ******** ******** @REM %1 is the name of SQL script file@rem SET SQL_NAME=%1@SET SQL_NAME=%1@SHIFT /1@REM SHIFT /1@REM P1... is parameters of SQLCMD,P1=%1...@rem c for count@set /a c=0@rem ******** ******** parameters for sql server login ******** ********  @set svrName=SERVERNAME\INSTANCENAME@set uname=your_name@set upwd=your_password@set db=YOURDBNAME@set s= %1 %2 %3 %4 %5 %6 %7 %8 %9@FOR %%A IN (%s%) DO @set /a c=c+1@if %c%==9  goto s9@if %c%==8  goto s8@if %c%==7  goto s7@if %c%==6  goto s6@if %c%==5  goto s5@if %c%==4  goto s4@if %c%==3  goto s3@if %c%==2  goto s2@if %c%==1  goto s1@if %c%==0  goto s0 :s9@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 p4=%4 p5=%5 p6=%6 p=%7 p8=%8 p9=%9 @goto end:s8@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 p4=%4 p5=%5 p6=%6 p=%7 p8=%8  @goto end:s7@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 p4=%4 p5=%5 p6=%6 p=%7  @goto end:s6@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 p4=%4 p5=%5 p6=%6  @goto end:s5@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 p4=%4 p5=%5  @goto end:s4@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 p4=%4  @goto end:s3@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  p3=%3 @goto end:s2@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 P2=%2  @goto end:s1@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME% -l60 -v P1=%1 @goto end:s0@sqlcmd -S%svrName% -U%uname% -P%upwd% -d%db% -i%SQL_NAME%  <span style="font-size: 11.8181819915771px; font-family: Arial, Helvetica, sans-serif;">-l60 </span>@goto end:end @echo off@set svrName=@set uname=@set upwd=@set db=@exit /b 0 

Call Method

Test. SQL

set nocount ongoselect '$(p1)' as p1 ,'$(p2)' as p2

Batch call

D: The test directory contains multiple SQL files for batch operation.

Run

Attached:

Download

How to Use sqlcmd to execute SQL statements in a batch processing script

You can use sqlcmd to execute SQL statements in a batch processing script. Although this command has many parameters, we do not need to fully understand it. here we will briefly introduce the following:
{-U login_id [-P password]} |-E trusted connection}] If-E is specified, the user name and password are not required. Of course, if-E is specified,-E is not required;
-S server_name [\ instance_name] database server, which must be specified if it is not on the local machine;
-D db_name: Database Name, required;
[-I input_file] [-o output_file] If SQL exists in a file, use-I to output it to the file using-o;
[-Q "line query"] [-Q "cmdline query" and exit] the input is a simple SQL statement without any files. We recommend using-Q, if you need to execute other actions after executing sqlcmd;
[-W remove trailing spaces] removes unnecessary spaces and the results are more compact.
When we execute SQL in a real script, we usually need to input and output variables.
The input variable is relatively simple, as shown below:
Sqlcmd-d test-Q "select * from dbo. Investment where investor = $ (x)"-v x = 'ibm '-W
This statement Selects all rows with the value of "investor" equal to "x" from the "Investment" table of the test database. It is noted that the variable x is included in $.
Then, use-v to define the value of x, 'ibm '.
-W determines that the output result does not contain any extra space.
If the value of x is not set in sqlcmd, the system will try to find it from other places, including system environment variables and user environment variables, and the variable value set with set before sqlcmd.
If your data does include $ {}, you do not want to replace the variable. You can use the-x option to disable replacement of the variable.
Sometimes, you want to get the SQL Execution results and save them to variables. For example, if your log system generates log files every day, you need to execute a script to process these log files and coexist in the database. Before processing, you must read the database to determine the day when the last processing was completed. You expect this to solve your problem:
Sqlcmd-d test-Q "select $ {x} = max (date) from dbo. logDates"-W
But this does not work. Because sqlcmd does not provide output variables.
But you can do this:
Sqlcmd-d test-Q "declare @ x nvarchar (8); select @ x = max (date) from dbo. logDates; print @ x;"-W
In this way, you can get a clean number without including the column name and other information.
Then you import the above results into a file:
Sqlcmd-d test-Q "declare @ x nvarchar (8); select @ x = max (date) from dbo. logDates; print @ x;"-W 1.txt
Now the most critical step is to write the file content into the variable:
Set/P myvar = <1.txt
/P indicates that the value of myvar needs to be input by the user;
The <1.txt table is read from 1.txt rather than from other places.
... The remaining full text>

Bat uses sqlcmd to execute a script file to create a database. The black screen disappears. How can I stop in the Command window and check for errors?

You can use cmd to open the dos command window,

Then you can execute bat?