Copy or addnew from an array

[Dick Minter]

The object is to update an indexed table (2000+ records) with any changes
from an new, imported set of records. My current approach is to use an
append query, which works but takes a relatively long time to run, and it
only appends unmatched records. A second, Update query is needed for changes
in existing records. An alternate approach would be to use getRows to create
an array to hold the imported data, then step though the array testing for an
index conflict in the target table, adding a new record where no conflict
exists, otherwise and updating selected fields in the exiting record.
First, could I expect a performance improvement with the alternate approach,
and second, is there a way to copy the array row into the target recordset
without setting each field value specifically; e.g. similar to the
"copyFromArray" function in ObjectPal?

DM

 

[Marshall Barton]

The object is to update an indexed table (2000+ records) with any changes
from an new, imported set of records. My current approach is to use an
append query, which works but takes a relatively long time to run, and it
only appends unmatched records. A second, Update query is needed for changes
in existing records. An alternate approach would be to use getRows to create
an array to hold the imported data, then step though the array testing for an
index conflict in the target table, adding a new record where no conflict
exists, otherwise and updating selected fields in the exiting record.
First, could I expect a performance improvement with the alternate approach,
and second, is there a way to copy the array row into the target recordset
without setting each field value specifically; e.g. similar to the
"copyFromArray" function in ObjectPal?

This kind of thing can depend on the details of what is
being updated.

If it's entire records that are either new or modified and
assuming both tables have matching primary keys, then you
can run two queries. First, one to delete the old records
that were modified and a second to append all the imported
records.

DELETE table.*
FROM table INNER JOIN import
ON table.pk = import.pk

INSERT INTO table
SELECT *
FROM import