>Sounds like you're doing "SQL delete then insert" in a different way.

I'm sorry to disappoint you, but the process (to me) appears to be simple.

The reason the keys must be kept for existing products is that the database and application is already in use round the world and the key is stored when a product is selected. When we distribute product updates then the stored key must correspond to the product they select. That means that if any part of the description, size, etc changes then the record is deleted and a new record with a new key is created.

The user will be given a message that the product is no longer available and will be presented with a list of products that meet their criteria (which is stored with the job).

Hope this clears things up.

As for the code - well here it is:


function UpdateTable
lpara tcTable, tcView, tcKeyFld, tcExclFld

local llOK
local lcFldList
local laFldList[ 1 ]

tcTable = iif( type( "tcTable" ) = "C", upper( alltrim( tcTable )), "" )
tcView = iif( type( "tcView" ) = "C", upper( alltrim( tcView )), "" )
tcExclFld = iif( type( "tcExclFld" ) = "C", upper( alltrim( tcExclFld )), "" )
tcKeyFld = iif( type( "tcKeyFld" ) = "C", upper( alltrim( tcKeyFld )), "" )

llOK = .f.

if !empty( tcTable ) and !empty( tcView ) and !empty( tcKeyFld )

* Build an expression that can be used in an sql to return the contents of
* all fields except those listed in the exlude field list
lcFldList = this.GetFldExpr( tcTable, tcExclFld )

* Create an expression list from the table - old data

select &tcKeyFld, &lcFldList as expr;
from (tcTable);
into cursor qOldData

* Create an expression list from the view - new data
this.UpdateProgress( 2, "Building list of new entries..." )

select &tcKeyFld, &lcFldList as expr;
from (tcView);
into cursor qNewData

* Determine which records should be deleted
* - records in old that aren't in the new
select &tcKeyFld;
from qOldData ;
where expr not in ( select expr from qNewData );
into cursor qRecs2Del

if _tally > 0

* Delete the old records
scan
lnKeyVal = eval( tcKeyFld )

this.DeleteRecord( tcTable, tcKeyFld, lnKeyVal )

endscan
endif

use in select( "qRecs2Del" )

* Determine which records should be added
* - records in new that aren't in the old
select &tcKeyFld;
from qNewData ;
where expr not in ( select expr from qOldData );
into cursor qRecs2Add

if _tally > 0

* Add the new records
scan

lnKeyVal = eval( tcKeyFld )
select (tcView)
locate for &tcKeyFld = lnKeyVal

scatter memo to laFldList

* Insert the new record (getting new key, record recycling, etc)
this.InsertRecord( tcTable, tcKeyFld, @laFldList )

select qRecs2Add

endscan
endif

use in select( "qRecs2Add" )

llOK = .t.
endif

* Update Complete

return llOK


This works well with tables that contain 50 thousand records but it had to stopped after an hour when it was working with 1 million records. I was wondering if there was a better method of achieving the same goal.


Jason