0

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

  1. 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.

  • 1
    I apologize but I am unsure what you mean. Do you mean you want to insert multiple records into a DE in one call? If so, you can only do this via API or via a FOR loop that will then loop through each Topic and call the insert function once per topic - inserting multiple rows. – Gortonington Jun 24 at 14:31
  • 1
    No need to apologize it my fault for writing my question so stupidly. I was trying to.post some code but it keeps breaking up so I wrote just the question. Yes that was exactly what I meant, general subscriber data in one DE, and topics in multiple rows by recordid. – Uroš Kenk Jun 25 at 16:08
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 = 'sample@sample.com',
    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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.