I will answer your second question first: I don't know. I don't currently have an Oracle box that I can play against. Perhaps someone else will be able to answer that question.

Now for the first question:

My personal preferance is to only update/add/convert what needs to be updated/added/converted. Since it appears that all records are going to be coming over everytime, my first question would be: Do you know which records are new/changed? If not, I only see two options:

1. Replace all data everytime.
2. Write a routine that checks every incoming record against the existing records (assuming you have a key to look up on), and checks to see if this is a change.

If you do have a unique key you can search on, then "adds" should be easy to identify by using a select statment such as:

select * from where not in (select from )

This will return a cursor of the records that exist in the incoming data but not in the existing data. In other words, the new ones.

I typically use the following structure:

1. Look for records that need to be added. (select * from where not in (select from )) Add them to the target table.
2. Look for records that need to be deleted. (select * from where not in (select from )) Delete them from the target table.
3. Look for all records that are changes. Hopefully I have a change flag in the incoming data. If so, I can use (select * from where in (select from ) and
Without knowing too much about your problem, it would appear that you have your work cut out for you. Conversions like this can be a real headache.

>>Bret,
>>
>>If it were me, I would try to get an ODBC connection to the Oracle tables and use Remote Views to write the transfers. It isn't much different from using Remote Views against SQL. When I was using Oracle at my last job, it came with a satisfactory ODBC driver that has to be installed on the machine. I had good luck with it. Just my 2 cents.
>
>Well what I am trying to cut down is the several read and writes that need to be performed.
>
>The conversion phase will go something like this:
>
>WRITE Oracle Dump Gets Produced
>READ Oracle dump is FTP'd to our system (135MB)
>READ Oracle Dump Gets Read into our Oracle System
>READ ODBC Data is read by VFP one record at a time (as this is a complete load each time)
>WRITE Conversion Process Writes the data into acceptable VFP tables.
>WRITE Master VFP Tables are copied over with new converted data tables.
>
>So what I am looking for is the method that would be the fastest to get the conversion done and it needs to be automated (done at night while no one is around).
>
>Which brings up another question on ODBC. How can I get my VFP ODBC connection to Oracle to save the password and quit asking me for it? The process cant be too automated if a password has to be input to get the job done.
>
>>
>>
>>
>>>I need some advice on how to go about doing this.
>>>
>>>My group was given the data as a dump from oracle and we loaded it into our Oracle system. Now I want to know what is the best way to get the data out of Oracle and into our VFP database. The data is huge as one table has 5 million records and the next size is 500,000 records. The data will be given to us twice a week and I am tasked to get the data out of Oracle and into our VFP system. As I have no Oracle experience, I was wondering what would be the best way to do this? Any suggestions would be appreciated, but if you do give one that involves doing something inside of Oracle can you provide a detail explanation. Because as I said I do not know Oracle. What I am hoping for is a fairly automated way of getting this done, so that it can run unattended at night.
>>>
>>>TIA

Brian McCord
President
Sagacity Systems