One-to-many insert/upsert from landing to separate DE's

Question

I'm bulding a preference center and having trouble with writing into two separate DE's.

We have two DE's:

1. Newsletter_Subscribers (EmailAddress, FirstName, LastName, OptIn, Country, IPAddress, DateSubscribed, DateModified, DateUnsusbscribed)

Inserted here would be subscribers general data (only one row per subscriber).

2. Newsletter_Subscriber_Topics (RecordID(concat Email + TopicID), EmailAddress, TopicID, DateSubscribed, DateUnsubscribed In here would be every topic in.separate row based on a recordid.

What I'm having trouble with is to write Topics in a seperate DE row by row (PK is RecordID) - I can only get a flat insert

Has anybody tried this before?

Tnx.

Answer 1

So to level-set. There is no way in AMPscript to do a batch upload. For those you would have to utilize a FOR loop and do multiple inserts.

For example:

%%[
  SET @newsSubsRes = InsertDE('Newsletter_Subscribers', 'EmailAddress',@EmailAddress, 'FirstName',@FirstName...etc...)

  FOR @i=1 to RowCount(@TopicRowset) DO

    SET @row = ROW(@TopicRowset,@i)
    SET @TopicID = FIELD(@row, 'TopicID')

    SET @newsTopicsRes = InsertDE('Newsletter_Subscribers_Topics', 'RecordID', CONCAT(@EmailAddress,@TopicID),'EmailAddress',@EmailAddress,'TopicID',@TopicID,...etc...)

  NEXT @i
]%%

This would do the single call for the Subscriber, and then loop through each of the topics you have (via @TopicRowset) and then inserts them into the DE one at a time. If the number of topics is significant, this could run into performance issues though.

For SSJS you can do a CORE function Rows.Add to push in a bulk insert. I do warn though that with any significant volume, this can start to have a performance degredation.

<script runat="server">
Platform.Load("Core","1");
var arrContacts =  [];

for (i=0;i<topicArr.length;i++) {
    
    var obj = {}
    var TopicID = topicArr[i];

    var obj = {"RecordID": EmailAddress + TopicID, "EmailAddress": EmailAddress, "TopicID": TopicID, "FirstName": FirstName}

    arrContacts.push(obj);
}

var topicDE = DataExtension.Init("Newsletter_Subscriber_Topics ");
topicDE.Rows.Add(arrContacts);
</script>

Another option if you do not want to do multiple inserts is to utilize WSProxy to push your data into the DE:

<script runat="server">
Platform.Load("Core","1");

/* Need to set your Subscribers variables here - e.g. EmailAddress, Firstname, etc. */
var EmailAddress = '[email protected]',
    FirstName = 'Sample',
    topicArr = ["cats","dogs","ferrets"],
    custKey1 = 'Newsletter_Subscribers',
    custKey2 = 'Newsletter_Subscriber_Topics';

var api = new Script.Util.WSProxy();
var insertArr = [];
var insertObj = {};

//This creates the object for Subscriber DE
insertObj.CustomerKey = custKey1;
insertObj.Properties = [
    {
        Name: 'EmailAddress',
        Value: EmailAddress
    },
    {
        Name: 'FirstName',
        Value: FirstName
    }
    /* Continue filling in properties here */
]


insertArr.push(insertObj)

for (i=0;i<topicArr.length;i++) {

    var topicID = topicArr[i];
    var insertObj = {};

    //This creates the objects for Topics DE
    insertObj.CustomerKey = custKey2;
    insertObj.Properties = [
        {
            Name: 'RecordID',
            Value: EmailAddress + topicID
        },
        {
            Name: 'EmailAddress',
            Value: EmailAddress
        },
        {
            Name: 'TopicID',
            Value: topicID
        },
        {
            Name: 'FirstName',
            Value: FirstName
        }
        /* Continue filling in properties here */
    ]

    insertArr.push(insertObj)
}


var options = {SaveOptions:[{'PropertyName':'*',SaveAction:'insertAdd'}]};
var res = api.updateBatch('DataExtensionObject',insertArr,options);

</script>

This will gather all of the records you want to insert (in this example, I used upsert) into an array (insertArr) and then via the updateBatch() function for WSProxy, it pushes them all into the appropriate DEs inside a single call.