Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Strategy for Inserting new records using SQL
Message
From
07/12/2003 11:16:02
 
 
To
07/12/2003 09:16:41
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00856690
Message ID:
00856701
Views:
16
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.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform