Search code examples
sqlbatch-filecmdsqlcmdlogfile

count the row affected put in Log File through batch file

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

enter image description here

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.

Solution

  • 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%