[SalesForce] Loading Massive data into Data Extension using API

No need to use the official API. Since there's an AMPscript function that returns this data, you could build a simple Cloud Page in your account that returns a JSON payload with this information:

%%[

var @key, @rowCount
set @key = QueryParameter("key")
set @rowCount = iif(empty(@key), 0, DataExtensionRowCount(@key))

]%%{ "rowCount": "%%=v(@rowCount)=%%"}

Working example:

https://pub.s7.exacttarget.com/1lnyuzpi01y?key=DataExtensionTest

Reference

• DataExtensionRowCount

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.