Hey Folks,

I have a situation where I need to update a table in a Database with info from another file (SDF text file, can convert to DBF or not as needed). The table to be updated (disp_data) is structured:

phone, C, 10
disp_code, C, 1
disp_date, C, 10

The file it comes from (dialer) will have this structure:

phone, C, 10
disp_code, C, 1
disp_date, C, 6

... as well as other info not pertinent to the issue at hand.

The difficult part arises from the update criteria. I need to ...

1) Update recs in disp_data that have matches in dialer, meaning that the recs in disp_data need to have the code and date overwritten,

2) Add recs to disp_data that are in dialer but not in disp_data already, and

3) Ignore recs in disp_data that are in it but not in dialer, meaning do not overwrite the code and date.

Normally, we're looking at 100,000 to 1,000,000 recs in the dialer file to be processed per update session (usually once per week).

I could use a SCAN loop, checking for each phone # and either adding or updating as needed, but that takes way too long, many hours for that quantity of recs.

I could use three SQL SELECTs, UNION the results, then overwrite and reindex the original disp_data table when I'm done, but I could see that taking forever as well as the disp_data table grows in size.

The best idea I've come up with so far (at least I think it is, for the moment) is to SELECT the matching recs, then REPLACE or UPDATE in disp_data with the result set, and SELECT the new recs, then APPEND them. However, this seems kind of kludgy to me, and I was hoping someone else had some better ideas?

Any takers? The provider of the best suggestion will be welcome to a beer or two anytime you come to Phoenix ...

TIA.

--
L8R

Dave