Is it possible to delete existing data as part of an import set

[Peter Bishop]

I have an import set working to populate a table from an external source. However the import is cumulative so that running the same file twice results in twice the records in the target table.

Is it possible to clear the target table prior to performing the import?

Thanks.

[Peter Bishop]

Hi Dave.

I agree with you and don't like the method I've implemented but it solves my problem. The issue with the coalesce option is, unless I'm mistaken, it doesn't delete target records that don't exist at the source.

Ideally, given that the table has a key column, I'd like:

- if key not exist at target, create

- if key not exist at source, delete

- otherwise, update

A solution like that would be fantastic.

To answer your other question, direct data access isn't possible at this time.

Thanks.

[Dave Smith1]

I agree with you and don't like the method I've implemented but it solves my problem.

Then I consider that a win.   Ultimately it's the desirable outcome, and it's been achieved, so - job done!

The issue with the coalesce option is, unless I'm mistaken, it doesn't delete target records that don't exist at the source.

You're not mistaken - coalesce simply prevents deduplication by making every operation an "upsert" (insert/update); without coalesce every operation is an attempted insert.

Ideally, given that the table has a key column, I'd like:

- if key not exist at target, create
- if key not exist at source, delete
- otherwise, update

Yeah, transforms won't do that, unless you're maintaining a list of deletion operations that can be deployed onto the platform (and even then it may likely be a 2-stage operation).

I'd try to script something that attempts a removal where key IS NOT IN (list of those just imported first) then consider the merge; it'll arrive at the same destination but won't be the "tear-down and replace" approach.

Ultimately, if your platform copes with the performance demands of your current approach, sounds like you've got a working solution.

To answer your other question, direct data access isn't possible at this time.

That's a pity, else any record changes on the remote platform could be synched across to yours upon change.   It's an exercise we run on the Advanced Admin course - raising an incident in one platform causes the trigger of a record creation in another.