count the row affected put in Log File through batch file

Question

I bet this is just too simple but i can't put it together:

how to count the row affected put it in a Log File while using batch file on running the script.

xxx_proc.bat

@echo off 

echo Running script in THENW

echo %DATE% tprdcr.sql executed > Logfile.txt

isql -UEMXXX2 -PEMXXX2 -SKXXXXXXDB02 -Jcp8x50 -w20x00 -itprdcr.sql >> Logfile.txt

echo tprdcr.sql Generated successfully

pause

EXIT /B

LogFile.txt

The result is quite many, so i just want it to display [nn rows affected]. Also if there will be error in one update statement it will just bypass the count and display the error.

Answer 1

I suspect there is something you could do with your .sql files to modify the output to make your life easier. But if you want to work with the log files as they are now...

Until you show what the errors look like, we can't suggest how to bypass on error.

Here is a pure batch method to get the total count:

set /a cnt=0
for /f "delims=( " %%N in (
  'findstr  /c:"^(1 row affected)" /c:"^([0-9][0-9]* rows affected)" "Logfile.txt"'
) do set /a cnt+=%%N
echo %%N rows affected

You could also use my JREPL.BAT utility:

jrepl "\((\d+) rows? affected\)" "total+=Number($1);false" /jmatch /jbeg "total=0" /jend "output.WriteLine(total+' rows affected')" /f "Logfile.txt"

You can put the above command in a FOR /F loop if you need to capture the total in a variable:

for /f %%N in (
  'jrepl "\((\d+) rows? affected\)" "total+=Number($1);false" /jmatch /jbeg "total=0" /jend "output.WriteLine(total+' rows affected')" /f "Logfile.txt"'
) do set total=%%N
echo %total%

Answer 2

If you want the original Logfile to remain intact, then you could use this:

@echo off
@setlocal EnableDelayedExpansion

::Other code here

set count=0
for /f "tokens=*" %%r in ('type Logfile.txt ^| findstr /i "row affected"') do (
for /f "tokens=1 delims=^( " %%c in ("%%r") do set /a count+=%%c
)
echo Total rows affected: !count!
echo Total rows affected: !count!>>Logfile.txt
pause
exit /b

Optionally, if you want to bypass writing each individual row affected to the log file, and just write a single line stating the number of rows affected, you might could use this instead:

@echo off
@setlocal EnableDelayedExpansion

::Other code here

set count=0
for /f "tokens=*" %%r in ('isql -UEMXXX2 -PEMXXX2 -SKXXXXXXDB02 -Jcp8x50 -w20x00 -itprdcr.sql ^| findstr /i "row affected)"') do (
for /f "tokens=1 delims=^( " %%c in ("%%r") do set /a count+=%%c
)
echo Total rows affected: !count!
echo Total rows affected: !count!>>Logfile.txt
pause
exit /b

Hope this helps.