I have two nearly identical database tables STOCK and STOCK_PREVIOUS that have the same number of items. I also have a STOCK_REMOTE table. I'm updating STOCK_PREVIOUS and STOCK_REMOTE if the "units" don't match in STOCK and STOCK_PREVIOUS. (Unfortunately STOCK does not have a datetime stamp and it's out of my control, otherwise I would simply check that for a "changed" record). This works fine and ia extemely fast using a simple set relation.

However, if a new record is inserted into STOCK, I'd like the that record to be inserted into STOCK_PREVIOUS through this program using a SQL statement.

What would the SQL code look like to accomplish this? Do I need to break the RELATION and rescan? Thanks in advance.

Here's the code so far:

OPEN DATABASE test
SELECT 1
USE stock
SET ORDER to number
SELECT 2
USE stock_previous ORDER 1
SELECT 1
SET RELATION TO number INTO stock_previous
SCAN
*If the units don't match
IF a.units<>b.units
*Update the remote_stock
UPDATE stock_remote ;
SET units=stock.units ;
where stock.number=stock_remote.number ;
AND stock.units<>stock_remote.units
*Update the local stock_previous
UPDATE stock_previous ;
SET units=stock.units ;
where stock.number=stock_previous.number ;
AND stock.units<>stock_previous.units
endif
ENDSCAN