>I am trying to append or update from 1 table to another. They both have the same structure. I cannot make changes to the source table as it beelongs to another company. The source table has no unique field, however, a combination of 3 fields is unique. So I set an index in the target table consisting of these 3 fields. I must evaluate each record in the source table, if it does not exist in the target table I append it. If it does exist, I must see if it has been updated and if so, update the appropriate fields. This is working o.k. but it is very slow as the target table continues to grow and my do while not eof() and seek commands seem to be the only way to make my updates. Any suggestions would be appreciated.
>
>for example the 2 tables have fields A - E.
>None are unique but A + B + C is unique
>
>use table 1
>do while not eof()
> store A + B + C to memvar
> use table 2
> seek A + B + C
> if found()
> if fields D + E dont match
> update
> endif
> else
> append record from table1
> endif
> skip
>enddo
There are several things you are doing here. First, you open table 2 every time through the loop. Also, use SCAN/ENDSCAN. I've rewritten your code
USE Table1 IN 0
USE Table2 IN 0 ORDER TAG MyIndex
SELECT Table 1
SET RELATION TO A + B + C INTO Table2
SCAN
IF FOUND("Table2") AND D + E don't match
Update
ELSE
INSERT INTO Table2
ENDIF
ENDSCAN
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer