The 5 ways of adding and updating records in SFMC using AMPscript and server-side JavaScript

The 5 ways of adding and updating records in SFMC using AMPscript and server-side JavaScript

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.

NameLengthNullable
SubscriberKey50No
EmailAddress254No
FirstName50Yes
LastName50Yes

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))
	
]%%

Pros

  • Simple
  • Straightforward

Cons

  • AMPscript does not allow generic code
  • Hard to debug

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))

]%%

Pros

  • Update and Add in one single method

Cons

  • Long and complex
  • Hard to debug

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>

Pros

  • Doesn’t require a CustomerKey

Cons

  • Uses separate Arrays for Keys and Values

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>

Pros

  • Short
  • Simple
  • Uses objects

Cons

  • Requires a CustomerKey instead of a Name
  • Uses Arrays for record matching

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>

Pros

  • Short and simple
  • Uses objects
  • Doesn’t require a CustomerKey
  • Detailed error message

Cons

  • Objects in Key-Value pairs

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.

  1. Great post. What i am interested in would be: How can you deal with the error messages that were caught by the catch clause. Currently you are only printing it via write, but wouldn’t it be better to write the exceptions into a dataextension? Would that make sense to try writing it into a dataextension, because the error might be caused by the inability to write to a dataextension? 😀

  2. I write a JSON structured error and I insert a record in an error log Data Extension with the error message, the date and the referrer URL. I use WSProxy method, as it doesn’t break the page if something goes wrong. I also have an Automation to notify me daily by email about the new records that were inserted in the error log Data Extension.

  3. Hi, Ivan, instead of send just one subscriber to a new data extension, can I move all my subscribers to a Data extension at once ?

  4. Hi, Ivan, congratulation about the content, I got a question, can I add into my data extension all subscribers from my list? Using this code that you created but with some “from” ( as SQL, just a guess), thanks so much

  5. I am getting this exception when using the InsertData Ampscript option within a LandingPage.
    “ExactTarget.OMM.FunctionExecutionException: An error occurred when attempting to execute an InsertData function call. See inner exception for details. Error Code: OMM_FUNC_EXEC_ERROR – from Jint –> — inner exception 1— ExactTarget.OMM.FunctionExecutionException: InsertData Function is not valid in content. This function is only allowed in a non batch context. Function: InsertData( ‘My_Target_DE’, ‘EmailAddress’, @EmailAddress, ‘FirstName’, @FirstName, ‘LastName’, @LastName, ‘SubscriberKey’, @SubscriberKey ) Error Code: OMM_FUNC_CONTEXT_ERR – from OMMCommon “

Comments are closed.

Salesforce Marketing Cloud
Up Next:

How to trigger a Journey API Entry event with AMPscript and server-side JavaScript

How to trigger a Journey API Entry event with AMPscript and server-side JavaScript