2

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?

Improve this question
2
  • You seem to be asking a variety of questions.... Do you want to: a) update a record in a DE, b) clear all data from a DE, c) upsert a record in a DE, or d) delete a record from a DE? All of those are possible with the SOAP API. Most are also possible via AMPScript or SSJS as well. Updating records requires that your DE have primary key(s).
    – Sam Whitmore
    Commented Aug 13, 2018 at 17:30
  • 1
    Goal is to overwrite Data Extension contents (all rows) with new content (x-amount of rows) coming from an API call. With no specific rows or ID's specified. So it's basic overwrite functionality that comes with SQL queries and File Imports. Rest of my comments are just detailing what can't be done, so there is just one question actually :)
    – Rain
    Commented Aug 13, 2018 at 18:06

2 Answers 2

Reset to default
6

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.

Improve this answer
3
  • Very well, thanks a bunch for confirming this and giving the headsup how it works :)
    – Rain
    Commented Aug 13, 2018 at 19:47
  • This method only deletes individual rows. You'd want to make a Perform call to clear all data from the data extension at once. Clear is also significantly more performant. I believe on the backend, Clear is performing a drop table and recreating a clean DE.
    – Sam Whitmore
    Commented Aug 14, 2018 at 22:16
  • I was unaware that clear even was an option in SFMC API - thank you for the information, that definitely seems to be a more efficient method. Adding reference into my answer
    – Gortonington
    Commented Aug 19, 2018 at 18:04
2

To clear a DE, you'll want to make an API call to perform a clear (completely removes all data from the DE), and then you can create new records.

Here's the SOAP documentation to clear a Data Extension: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-apis.meta/mc-apis/clearing_data_from_a_data_extension.htm

That's the API equivalent of this UI feature: https://help.salesforce.com/articleView?id=mc_cab_clear_data_from_a_data_extension.htm&type=5

Then you can make a create record calls on the DE.

I can also provide SSJS code to perform the clear and insert records, but sounds like you're using a third party to manage the data and inject to SFMC via API. Using the Clear will make it so you can use the final Data Extension without needing the separate staging DE and query.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .