>- see footer for list info -< Is there any rule to new/updated records.
I.E. Do they upload a new file (with a different filename) when there are new records, or do they just re-upload the same file each time, and you have to see if it contains updates, new records or both? If new records are always in a new file, then you can keep track of previous filenames so this will tell you whether you need to do an INSERT or an UPDATE. You will have some advantage with using an SP for the update/add record queries. But Either way your going to have to do the looping ove rthe file with CF as the file will be on the web server so the DB server will not be able to connect to it with a SP directly. Russ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam Sent: 09 September 2006 16:36 To: 'Coldfusion Development' Subject: [CF-Dev] Import of 10k records from a tab delimited txt Importance: High >- 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 >-< _______________________________________________ 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 -<
