Setting up a batch search for partnumbers in an inventory database. Idea is to allow batch upload of many partnumbers – could be up to several hundred that are then checked against an online database that has about 50,000 records to come up with matches.
The batch input will be a simple text file that I can read through with say a textfile readline stream. That’s easy. The trick seems to be to then search the large database for each partnumber to get a match or not in an efficient manner.
The most obvious is to get the recordset of the database. Need all records but not all fields. Then iterate through the recordset in a match check with a movenext til EOF. That’s relatively slow.
I thought of doing a recordset.find (partnumber=search) to be a faster way of searching the recordset. Is it?
The other would be reselect the recordset with a filter of partnumber. That would require a reselect for each check.
Is there a SQL select method that would allow an intrinsic multiple item search similar to the Include where you list a set of variables.
Open to ideas on this. Using ASP, VBscript on MSSQL7 ADO.