Update Records from Files

#1

I have a bit of a need that seems to be outside the scope of the Update Database Table action.

Currently I have a project that creates a dataset based on multiple other inputs. I am looking for a way to update that dataset with new records and updated existing records so I don’t have to rebuild the whole dataset (10 million records) each time - which is what I’m doing today…just re-running the whole project and creating a new dataset.

Is there a more efficient way to simply add new records / update existing records without saving the previous run, matching on the “new delta changes (which includes new and updates)”, then deleting those on the delta file from the previous run, and then appending the new delta records…save that as the new dataset…and repeat at the next run.

Again this is not in a database - right now my project saves as a .dset but I’m not forced to keep it as a .dset…but I also don’t have a database to do this in either.

#2

Typically, an “upsert” (update + insert) operation in EasyMorph is performed with the help of two derived tables:

In one derived table, use the “Keep matching” action to keep the rows that need to be updated.

In the other derived table, use the “Keep matching” action in the “mismatch” mode to keep only the new rows.

Then, re-assemble a new table from the existing dataset by preserving the rows that don’t need to be changed (rows that need to be updated should be excluded from them), appending the rows that are updated, and finally appending the new rows.