Hi Ben, CF 5 + has something called a query of queries which allows you to create a query from two existing queries in the ColdFusion page, however, somehow I don't think this is what you want. We could probably provide more help if you could tell us what kind of DB's these are -- if they're both MS Access or they're both MySQL or something else then somebody on the list can probably recommend a good way of doing this. Off the top of my head I'd suggest trying to get all your zip-code data into your user info database -- I know access will allow you to export a table to another Access DB, and most other databases and database servers like MS SQL Server and Oracle allow something similar. Once you have all the data in a single DB then there is usually a way to populate the tables with often as few as a single query.
hth s. isaac dealey 954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > I have two databases, the first contains user information. > Some, but not > all users have provided their area codes and cities. The > second database is > a ZIPcode lookup with info on the city, state, areacode, > zip, etc. > The goal is to populate the user database with the most > accurate ZIP code > data. (I know it won't be at all perfect, but better than > nothing.) > I've come up with this code -- but it's extremely slow and > probably not the > best way to do things. I'm just learning this stuff. Can > anyone suggest a > better way to do this? Is it possible to combine queries > from two different > databases? > -B > <!--- select a userid, city, areacode where the areacode > and city are not > empty ---> > <cfquery name="GetUserID" datasource="users"> > SELECT UserID, areacode , City > FROM tUsers > WHERE NOT areacode IS NULL and NOT City IS NULL; > </cfquery> > <cfloop query="GetUserID" startrow="1" endrow="1000"> > <!--- get the zipcode, City, State from ZipCodes based on > the areacode ---> > <cfquery name="GetZipCode" datasource="zipcodes"> > SELECT ZipCode, City, StateCode > FROM tZIPCODES > <!--- make sure the zipcode is the "standard" type, the > city is close to > what the user has entered and citytype is D - Default City > Name - > recommended by the USPS ---> > WHERE AreaCode = '#GetUserID.areacode #' and City like > '%#GetUserID.City#%' > and ZIPCodeType = 'S' and CityType = 'D' > </cfquery> > <!--- make sure a value was found otherwise skip ---> > <cfif GetZipCode.recordcount NEQ 0> > <!--- now update the tUsers table with new values ---> > <cfquery name="UpdateZip" datasource="users"> > Update tUsers > SET Zip = '#GetZipCode.ZipCode#', > City = '#GetZipCode.City#', > State = '#GetZipCode.StateCode#' > WHERE UserId = #GetUserID.UserID# > </cfquery> > </cfif> > </cfloop> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Archives: > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index. > cfm?method=subscribe&forumid=4 > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Get the mailserver that powers this list at > http://www.coolfusion.com > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > ubscribe.cfm?user=633.558.4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
