>>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

---

Hi Jason,
I tried to interpret code and not sure about " where &tcKeyFld not in (select expr ...)". I expected it to be " where &tcKeyFld not in (select distinct &tcKeyFld ...). If that expectation is true w/o another dissappointment :)

* It seems to mean :
*!*	* Recdels
*!*	select &tcKeyFld, &lcFldList as expr;
*!*	from (tcTable);
*!*	where &tcKeyFld not in ( select &tcKeyFld from (tcView) ) ;
*!*	into cursor qRecs2Del

*!*	* RecsAdd
*!*	select &tcKeyFld, &lcFldList as expr;
*!*	from (tcView);
*!*	where &tcKeyFld not in ( select &tcKeyFld from (tcTable) )
*!*	into cursor qRecs2Add

* Instead of SQL could be translated to :

* Delete the old records
scan
  lnKeyVal = eval( tcKeyFld )
  if !seek(lnKeyVal, (tcView),"myTag")
     this.DeleteRecord( tcTable, tcKeyFld, lnKeyVal )
  endif
endscan

* Add new recs
select (tcView)
scan for !seek(&tcKeyFld, tcTable, "myTag")
  scatter memo to laFldList
* Insert the new record (getting new key, record recycling, etc)
  this.InsertRecord( tcTable, tcKeyFld, @laFldList )
endscan

Cetin