Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Running a Query Using a Text File for Input

By Roy Carlson,

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:
  1. change the path to "YOURFOLDER" in custID.vbs
  2. change the server name, username and password in the custID.bat
  3. run the custID.vbs from the Command Prompt with "cscript custID.vbs"
  4. check the custID.qry with "notepad custID.qry". It should have a query for each custID.
  5. run the batch file "custID.bat
  6. 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.

Total article views: 15113 | Views in the last 30 days: 12
 
Related Articles
FORUM

query execution order

query execution order

FORUM

Query performance for Ordering

Query performance for Ordering

BLOG

SQL Query Result Order

Every time an SQL query is run without the ORDER BY clause, the sort order of the results returned i...

FORUM

Query Order

while run the query the order of data will be different in every run.

FORUM

order by ??

Order by Query

Tags
miscellaneous    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones