How high is up?  Well everyone knows it is twice as high as it is half as high. Weird question and even weirder answer but that seems to be the way of SQL.  The users relish the idea of stumping the SQL person. Take the case of a user who requested the order amount for all the orders placed by specific users over the last five years and gave out the list of customer ids - 17,000 of them - out of a total field of 300,000+. Yikes!

No problem we add a new table and DTS the text data. Oh! Oh! This is a large third party application of which the MS SQL db is integral to. We can't create a table without causing an issue with the maintenance agreement. We have rights to query the database using Query Analyzer or Crystal Reports, but that is about it.

We could sit there and run 17,000 queries adding the where clause for each customer id. Not a good plan. We could array the where clause using an IN array. The user wanted the customer id, their name, order number, date of order and order amount. The data in real life spanned three tables requiring joins.

To demonstrate another solution we will use the Northwind database and this starting query:

SELECT
     Customers.CustomerID AS ID,
     Customers.CompanyName AS COMPANY,
     Customers.City AS CITY,
     Orders.OrderDate AS [DATE],
     Orders.Freight AS FREIGHT
FROM Customers
     INNER JOIN
     Orders ON Customers.CustomerID = Orders.CustomerID
or as I prefer using aliases:
SELECT
     C.CustomerID AS ID,
     C.CompanyName AS COMPANY,
     C.City AS CITY,
     O.OrderDate AS [DATE], 
     O.Freight AS FREIGHT
FROM Customers C 
     INNER JOIN
     Orders O ON C.CustomerID = O.CustomerID
Next we have the list of CustomerIDs - definitely not 17,000 but enough to get the point of all this.
custID.txt
ALFKI
ANTON
AROUT
BERGS
BOLID
BONAP
BSBEV
CACTU
CONSH
DOGGY
FOLIG
FOLKO
FRANK
FRANR
FRANS
FURIB

Lets cut-and-paste these names into a text file called custID.txt. Lets also make to other files but keep them empty - custID.qry and custIDResult.txt. Copy and save as custID.vbs the script below in the same folder. (The files are also attached at the bottom in a zip file.)
The next part is VBScript code. No dont run away. This is not a big deal - trust me - I am not in politics.

custID.vbs

On Error Resume Next
' Read text file line by line
Const FOR_READING = 1
Const FOR_APPENDING = 8
strFilename = "c:\YOURFOLDER\ custID.txt "
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilename, FOR_READING)
Do Until objFile.AtEndOfStream
     strLine = objFile.ReadLine
' This shows the custIDs in the command prompt
     Wscript.Echo strLine
If LEN(strLine) > 0 Then
' Write data to a text file
Set objFSOW = CreateObject("Scripting.FileSystemObject")
Set objFileW = objFSOW.OpenTextFile _
("c:\YOURFOLDER\custID.qry", FOR_APPENDING, True)
objFileW.WriteLine "SET NOCOUNT ON SELECT C. CustomerID, C. CompanyName" &_
   ", C.City, O. OrderDate, O. Freight FROM Customers C INNER JOIN Orders  O " &_
   " ON C.CustomerID = O.CustomerID WHERE C.CustomerID = '" & strLine & "'
SET NOCOUNT OFF"
End If
objFileW.Close
Loop
objFile.Close

All you have to do for the above is make or pick a folder for the files to reside in and change to name of YOURFOLDER - in both locations - to the name of your folder.

Ok you can skip this next part if you dont want an explanation of the VBScript.   The two constants - const - just make it easy to see that the code is for either reading or appending to an "existing"file.  All things starting with "str" are string variables. We make objects with the Set command using the File Scripting Object.  The first bit loops through the CustID.txt file reading each line - strLine - which is the CustomerID.  For each CustomerID the value is added to the query.  Then the new line containing the full query is appended to the file - custID.qry.  Then all files are closed.

NOTE:  You cannot run this script from Query Analyzer.  It is run from a command line using cscript. Example:

     C:\YOURFOLDER\>cscript custID.vbs

DO NOT RUN THIS WITHOUT THE cscript prefix Windows default is wscript which will require you to ok a lot of message boxes for each custID.   17,000 would be a lot of clicks.

Comments about the query itself:  I have added SET NOCOUNT code to the front and rear of the query, because I really dont want to know how many rows are returned.  If your search requires you to know if no rows were returned then you might want to delete the SET NOCOUNT ON and OFF.  We have another query we run using sql NOT EXISTS which reports on the missing.

Now comes the good old batch file which we run during minimum activity time.  custID.bat which consists of:

custID.bat


osql /U YOURUSERNAME /P YOURPASSWORD /d NORTHWIND /S YOURSERVER /h-1
   /i "c:\YOURFOLDER\custID.qry" /o "c:\YOURFOLDER\custIDResult.txt"
This uses SQLs osql command to sign on using YOURUSERNAME AND YOURPASSWORD to YOURSERVER and the NORTHWIND database for this example. If you have not used osql before open "Books On Line." Type in "osql utility." You will get info on this tool. (Please change the YOURUSERNAME, YOURPASSWORD, and YOURSERVER as required.)
For every line in the c:\YOURFOLDER\custID.qry, a query is run, appending the data to the file c:\YOURFOLDER\custIDResult.txt. The custIDResult may not be pretty but we run the file into MS WORD and using a macro to remove extra spaces, replacing hard returns followed by a comma with a comma and so on the file cleans up nicely to a comma delimited file. This is saved as a csv file which we import into Excel, Access or Crystal Reports to get the needed reports.
The sequence of events is:
change the path to "YOURFOLDER" in custID.vbs
change the server name, username and password in the custID.bat
run the custID.vbs from the Command Prompt with "cscript custID.vbs"
check the custID.qry with "notepad custID.qry". It should have a query for each custID.
run the batch file "custID.bat
check the custIDResult.txt and clean up with search and replace with Word to the format of your choice.
We have learned that with a text file of pre-selected data we can read the file into a query command file that can be processed by an osql command into a delimited text file for reporting. It certainly beats typing in 17,000 queries, but remember it is not the only way to accomplish this feat.
zip file with all documents.
P.S. There is no Doggy in either the window or Northwind.