how can i have batch read a list of user names and use those in an sql statement?

Question

Currently I have the following batch code to read 1 user name, and use it in sql

@echo on
cls
set userID=
for /F %%i in (UserID.txt) do set userID=%userID% %%i
sqlcmd -S server -d database -U username -P password -v userID=%userID% 
                -i "sqlQuery.sql" -s "," > "\output.csv" -I -W -k

The SQL query that is called is as follows

SELECT userId, COUNT (*) AS number 
FROM table 
WHERE userId = '$(userID)' 
GROUP BY userId 
ORDER BY userId desc

What I am looking for is if I have a list of user names in the text file it will dynamically change the WHERE statement to be

WHERE userId = '$(userID1)' OR userId = '$(userID2)' etc....

Answer 1

I haven't worked much with SQL scripts so I'm not sure if returns will cause a problem but this will generate what you need.

I used this input in a file called userID.txt:

steve,joe,fred,jason,bill,luke

ran it through this code:

@echo off
setlocal enabledelayedexpansion
set count=0
for /F "tokens=* delims=," %%G in (userID.txt) do call :loop %%G
:loop
if "%1"=="" goto :endloop
set /a count+=1
set userid%count%=%1
SHIFT
goto :loop
:endloop
set totalusers=%count%
set /a totalusers-=1

echo SELECT userId, COUNT (*) AS number FROM table WHERE ( > sqlQuery.sql
set count=0
:where_gen_loop
set /a count+=1
if !count! gtr !totalusers! goto endwhere_gen_loop
echo userId = '$(!userid%count%!)' OR>> sqlQuery.sql
goto where_gen_loop
:endwhere_gen_loop
echo userId = '$(!userid%count%!)'>> sqlQuery.sql
echo ) >> sqlQuery.sql
echo GROUP BY userId ORDER BY userID desc >> sqlQuery.sql

that generated this output in sqlQuery.sql:

SELECT userId, COUNT (*) AS number FROM table WHERE ( 
userId = '$(steve)' OR
userId = '$(joe)' OR
userId = '$(fred)' OR
userId = '$(jason)' OR
userId = '$(bill)' OR
userId = '$(luke)'
) 
GROUP BY userId ORDER BY userID desc 

and is then accessed by the end of the batch:

sqlcmd -S server -d database -U username -P password -i "sqlQuery.sql" -s "," > "\output.csv" -I -W -k

endlocal

Answer 2

All you need to do is modify your sql query to use an IN clause, and then format your variable properly. You also must use delayed expansion within the FOR loop because %userID% is expanded only once when the FOR statement is parsed, whereas !userID! is expanded at execution time for each loop iteration.

sqlQuery.sql:

SELECT userId, 
       COUNT(*) AS number
  FROM table
 WHERE userId in( $(userID) )
 GROUP BY userId
 ORDER BY userId desc

batch script:

@echo on
setlocal enableDelayedExpansion
set userID=
for /f %%i in (UserID.txt) do set "userID=!userID!,'%%i'"
sqlcmd -S server -d database -U username -P password -v userID="%userID:~1%" -i "sqlQuery.sql" -s "," > "\output.csv" -I -W -k

Note that the sql variable definition is using a batch substring operation to eliminate the leading comma.

The userID variable will end up looking something like 'userID1','userID2','userID3'

If there are too many IDs in the input file to fit within the ~8k environment variable limit, then you will need to dynamically build your sql script. At that point, you might as well eliminate the sql variables and simply use string literals.

@echo on
setlocal enableDelayedExpansion
set "delim="
>"sqlQuery.sql" echo SELECT userId, COUNT(*) AS number FROM table WHERE userId in(
for /f %%i in (UserID.txt) do (
  >>"sqlQuery.sql" echo !delim!'%%i'
  set "delim=,"
)
>>"sqlQuery.sql" echo ) GROUP BY userId ORDER BY userId desc
sqlcmd -S server -d database -U username -P password -i "sqlQuery.sql" -s "," > "\output.csv" -I -W -k