[SalesForce] Overwrite Data Extension with API call

Has anyone figured out a way to overwrite Data Extension rows via API call?

There isn't anything for it in documentation, neither SOAP API, SSJS, AMPscript, WSProxy or GTL seem to have an option to send data and overwrite everything in target Data Extension.

This boolean function seems to be supported only with SQL and File Import activities.

Anyway, to clear Data Extension via API call I would need to specify some unique ID's which isn't possible at the moment.

So currently I'm using API to send data to DE1 which is then used by SQL query to pass it over (by overwriting) to DE2.

Any suggestions?

Best Answer

There is not a single option for this. Even the overwrite function inside of SQL is not truly a single operation. It is actually using 2 different operations behind the UI 'curtain'.

To do it via the API, you will first need to run a Delete Method on your DE (sample from SFMC Docs). You then will do an ADD Method on your DE to insert the new records (sample from SFMC Docs).


EDIT

Thanks to @SamWhitmore, there is an easier way than utilizing the DELETE METHOD, by Performing a Clear to the whole DE at once - similar to what is done via the UI. (https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-apis.meta/mc-apis/clearing_data_from_a_data_extension.htm)


You can also do this via AMPScript if you add a 'reference column' to your DEs - I usually insert a column named 'Secret' and set it to a value of 1. This way you can always lookup or interact with all your columns by setting the 'WHERE' statement to Secret = 1.

You would then use the DeleteDE() and InsertDE() functions to 'Overwrite'.

This can also be done via SSJS the same way.