This article explains the 5 ways of adding and updating records with AMPscript and server-side Javascript in Salesforce Marketing Cloud.

What’s the point?

As Marketing Cloud developers, we have to know at least a couple of ways for adding and updating records in a Data Extension.

But what’s the point of knowing them all?

Remember, knowledge is power! Each of the methods presented below offers a different approach to coding and may even give us a hint on how to approach other methods available in Marketing Cloud.

Before we start

Since we are going to be writing in a Data Extension, let’s create one with the following structure and name it Customers.

Name	Length	Nullable
SubscriberKey	50	No
EmailAddress	254	No
FirstName	50	Yes
LastName	50	Yes

We don’t have to assign a Primary Key, but for the purpose of this example SubscriberKey and/or EmailAddress can be assigned as such.

Ready to get started? Let’s see what methods are out there.

AMPscript Functions

%%[
	
    SET @SubscriberKey = GUID()
    SET @EmailAddress = 'john.smith@mail.com'    
    SET @FirstName = 'John'
    SET @LastName = 'Smith'

    /*********************** ADD ***********************/

    SET @AddedRowCount = InsertData(
        'Customers',
        'SubscriberKey', @SubscriberKey,
        'EmailAddress', @EmailAddress,
        'FirstName', @FirstName,
        'LastName', @LastName
    )

    OUTPUTLINE(CONCAT("Rows added: ",@AddedRowCount))

    /********************* UPDATE *********************/
	
	SET @SubscriberKey = '123456'

    SET @UpdatedRowCount = UpdateData(
        'Customers', 1,
        'SubscriberKey', @SubscriberKey,
        'EmailAddress', @EmailAddress,
        'FirstName', @FirstName,
        'LastName', @LastName
    )

    OUTPUTLINE(CONCAT("Rows updated: ",@UpdatedRowCount))
	
]%%

Verdict

This is the default AMPscript method. Easy, simple and straightforward. The drawback is the pesky Error 500, which appears on every syntax error, among other things.

---

AMPscript and Data Extension API

%%[

    SET @SubscriberKey = '123456'
    SET @EmailAddress = 'john.smith@mail.com'
    SET @FirstName = 'John'
    SET @LastName = 'Smith'

    SET @DataExtensionObject = CreateObject('DataExtensionObject')
    SetObjectProperty(@DataExtensionObject, 'Name', 'Customers')

    SET @prop = CreateObject('APIProperty')
    SetObjectProperty(@prop, 'Name', 'SubscriberKey')
    SetObjectProperty(@prop, 'Value', @SubscriberKey)
    AddObjectArrayItem(@DataExtensionObject, 'Properties', @prop)

    SET @prop = CreateObject('APIProperty')
    SetObjectProperty(@prop, 'Name', 'EmailAddress')
    SetObjectProperty(@prop, 'Value', @EmailAddress)
    AddObjectArrayItem(@DataExtensionObject, 'Properties', @prop)

    SET @prop = CreateObject('APIProperty')
    SetObjectProperty(@prop, 'Name', 'FirstName')
    SetObjectProperty(@prop, 'Value', @FirstName)
    AddObjectArrayItem(@DataExtensionObject, 'Properties', @prop)
	
	SET @prop = CreateObject('APIProperty')
    SetObjectProperty(@prop, 'Name', 'LastName')
    SetObjectProperty(@prop, 'Value', @LastName)
    AddObjectArrayItem(@DataExtensionObject, 'Properties', @prop)

    SET @updateOptions = CreateObject('UpdateOptions')

    SET @saveOptions = CreateObject('SaveOption')
    SetObjectProperty(@saveOptions, 'SaveAction', 'UpdateAdd')
    SetObjectProperty(@saveOptions, 'PropertyName', '*')
    AddObjectArrayItem(@updateOptions, 'SaveOptions', @saveOptions)

    SET @de_statusCode = InvokeUpdate(
		@DataExtensionObject, 
		@de_statusMsg, 
		@errorCode, 
		@updateOptions
	)

    OUTPUTLINE(CONCAT('Status: ',@de_statusMsg))

]%%

Verdict

This is the AMPscript way of working with objects. It works fine but it’s excessively complicated and long. Avoid this method at all costs, unless you really, REALLY don’t want to use server-side Javascript.

---

Platform Functions

<script runat="server">

    Platform.Load("core", "1.1");

	/*********************** ADD ***********************/

	var addedRowCount = Platform.Function.InsertData(
        'Customers',
        ['SubscriberKey', 'EmailAddress', 'FirstName', 'LastName'],
        [GUID(), 'john.smith@mail.com', 'John', 'Smith']
    );

	Write('Rows added: ' + Stringify(addedRowCount));

	/********************* UPDATE *********************/

	var updatedRowCount = Platform.Function.UpdateData(
        'Customers',
        ['SubscriberKey', 'EmailAddress'],
        ['123456', 'john.smith@mail.com'],
        ['FirstName', 'LastName'],
        ['John', 'Smith']
    );

	Write('Rows updated: ' + Stringify(updatedRowCount));

</script>

Verdict

Although it’s rather simple to grasp, we need to separate the data into Key-Value pairs, which is far from ideal and makes it more complex that it
needs to be.

---

Core library functions

<script runat="server">

    Platform.Load("core", "1.1");

    var de = DataExtension.Init('AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE');
	
	/*********************** ADD ***********************/

	var payload = {
		SubscriberKey: GUID(),
		FirstName : 'John',
		LastName : 'Smith',
		EmailAddress : 'john.smith@mail.com' 
	};

	var addedRowCount = de.Rows.Add(payload);

	Write("Rows added: " + Stringify(addedRowCount)); 
	
	/********************* UPDATE *********************/

	var payload = {
		FirstName : 'John',
		LastName : 'Smith'
	};

	var updatedRowCount = de.Rows.Update(
		payload, 
		['SubscriberKey','EmailAddress'], 
		['123456','john.smith@mail.com']
	);

	Write("Rows updated: " + Stringify(updatedRowCount));

</script>

Verdict

This method is great but it’s a glass half full. On one hand, it uses a simple Javascript object for sending the data. On the other hand, it uses the Key-Value separation in Arrays for the record matching, which is far from ideal.

---

WSProxy and SOAP API

<script runat="server">

    Platform.Load("Core","1");

	var api = new Script.Util.WSProxy();

	/********************* UPDATE/ADD *********************/

	var payload = {
		Name: 'Customers',
		Properties: [
			{
				Name: 'SubscriberKey',
				Value: 1
			},
			{
				Name: 'EmailAddress',
				Value: 'john.smith@mail.com'
			},
			{
				Name: 'FirstName',
				Value: 'John'
			},
			{
				Name: 'LastName',
				Value: 'Smith'
			}
		]
	};

	var options = { 
		SaveOptions: [{
			'PropertyName': '*',
			/* 'PropertyName': 'SubscriberKey, EmailAddress', */
			SaveAction: 'UpdateAdd'
		}]
	};

	api.updateItem('DataExtensionObject', payload, options);

</script>

Verdict

This method is great. It’s quick, simple, includes update and add functions, doesn’t require a CustomerKey. The only drawback is that we have to split the data into Key-Value pairs.

---

Which method is better?

All of them are good enough to do the job and when it comes to performance, there is no significant difference.

That being said, I still highly recommend using the SSJS methods because it can be molded as your heart desires, they are easy to debug and offers many coding possibilities.

Here is an example of how to use the Core Library functions in an object-oriented piece of code.

<script runat="server">

    Platform.Load("core", "1.1");

    try {
      
        var update_payload = {
            Where: {
                SubscriberKey : '123456',
                EmailAddress : 'john.smith@mail.com' 
            },
            Data: {
                FirstName : 'John',
                LastName : 'Smith'
            }
        };
      
      	var add_payload = {
            SubscriberKey: GUID(),
            FirstName : 'John',
            LastName : 'Smith',
            EmailAddress : 'john.smith@mail.com' 
        };

        var record = new DataExtensionActions('Customers');
            
        record.update(update_payload);
      	record.add(add_payload);

    } catch(err) {
        Write(Stringify(err));
    }

    function DataExtensionActions(name) {

        var req = DataExtension.Retrieve({ 
			Property: "Name", 
			SimpleOperator: "equals", 
			Value: name
		});
		
        var de = DataExtension.Init(req[0].CustomerKey);

        this.add = function(obj) {

            var data = (obj.Data != null) ? obj.Data : obj ;
        
            var addedRowCount = de.Rows.Add(data);
    
            Write("Rows added: " + Stringify(addedRowCount)); 
        
        }

        this.update = function(obj) {

            for(k in obj.Where) {

                if(obj.Data[k] != null) delete obj.Data[k];

            }

            obj.Where = splitKeyValue(obj.Where);
        
            var updatedRowCount = de.Rows.Update(
				obj.Data, 
				obj.Where.Keys,
				obj.Where.Values
			);
    
            Write("Rows updated: " + Stringify(updatedRowCount)); 
        
        }

        function splitKeyValue(obj) {
            var out = { Keys: [], Values: [] };
            for(k in obj) {
                out.Keys.push(k);
                out.Values.push(obj[k]);
            }
            return out;
        }

    }

</script>

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.