>- see footer for list info -< Dear All, I have a system which needs to take a number of records (upwards of 10k to 12k) of peoples contact numbers from a surgery DB. Each patient may have a mobile and landline so the format of the file is as follows:
Pat ID NHS Number Home Mobile Email ###### ###### ####### 0 0 OR Pat ID Number Type Number ##### Mobile Phone ########### ##### Telephone - Home ########### The numbers have been removed above to protect the innocent!!! There are a couple of different text formats as not all surgeries use the same patient management system and they are all a pain in the bum to get info out of. The issue is, the surgery can upload this file at any time using a form on a secure website. This is being done using the Flash Forms cf_flashUpload from ASFusion. Getting the file up to the site is not a problem. The problem is when I need to import these records into the DB. This file might be an initial insert in which case I need to push all records into the DB, equally the file might be an updated file from a previous upload in which case only the numbers that have changed need to be updated. Question is, what is the most efficient way to do this. I am using CFMX7 and MS SQL 2000. My Stored Proc knowledge is not brilliant but would welcome any guidance you can provide. At the moment I simply step through the file record by record and check if the patient info is already in the DB, if it is and the data is different, update the record, if not in the DB, insert it. As my knowledge of Store proc is not good the logic for this is being handled by CF and not in a single Stored proc as might be more efficient. Ideally, I need the system to receive the file, run the import and then return the status of the import to the user, i.e. how many records where added, updated etc, as this may take a few seconds I would like to have a screen which polls the system to see if the import has completed but this is not a must, just thinking aloud. Look forward to your input. With Regards, Lee _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
