Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Strategy for Inserting new records using SQL
Message
De
07/12/2003 11:16:02
 
 
À
07/12/2003 09:16:41
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00856690
Message ID:
00856701
Vues:
15
In this particular case you can get away with a slight modification to your SCAN loop:
select stock
set order to number in stock_previous
set relation to number into stock_previous
* note: the FOR condition covers EOF([stock_previous]) implicitly
scan for stock.units <> stock_previous.units
   if eof([stock_previous])  && new record -> insert
      insert into stock_previous ...
      insert into stock_remote ...
   else && unit count out of date -> update
      replace units with stock.units in stock_previous
      update stock_remote ...
   endif
endscan
Note, however, that this loop will not detect orphaned records (that is, records that have been deleted in the master table but that still exist in the replicas). The easiest way out is to decree that unit count be set to 0 instead of deleting records. If that is not possible then you need to walk the stock and stock_previous tables in tandem or do the orphan detection separately (SQL select).

Also, the pairs of update/insert statements should be protected by a transaction because the integrity of your data depends on stock_previous being a faithful 'shadow' of stock_remote.

P.S.: you may want to use a DO WHILE loop instead of a SCAN loop because SCAN seems to do some aggressive locking; DO WHILE in connection with SKIP seems to be more multi-user friendly.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform