How can I get Rows from a specific Data Extension and add them to another one using SSJS?

Question

I want to insert the rows from an existing data extension with the same column structure into another one using SSJS within Automation-Studio. I am using SSJS because I want to add them dynamically, where SQL cannot be a solution because you have to select the target Data-Extension manually.

So first I tried to select the DataExtension and add its rows into another DataExtension using the following code:

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

    var submitsDe = DataExtension.Init("xxxxx-xxxx-xxxxx-xxx-xxxxxxxxx")
    var data = submitsDe.Rows.Retrieve(); // Get all rows from source DE as Array
    var anotherDe = DataExtension.Init("another_de")
    anotherDe.Rows.Add(data) // This should work because Add() function also accepts array of objects source: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_dataExtensionRowsAdd.html
    </script>

I added this script to my data automation and ran it and it also says that it ran successfully, but the new data extension did not add any new rows. I have also tried to debug the code by testing it within a cloud page to see if I really get an array of objects with all the record entries from the "submitsDe" table.

So the code is executed but I don't see any result. Is there any other way to debug the code if there is no other solution?

Thank you in advance!

Answer 1

There could be a few possible reasons:

• You need to initialize the DEs using their external keys. Make sure all DEs are initialized by their external key.

• These two DEs might not have the same structure, even if they seem similar. I would suggest creating a new DE to replace the second one for testing. Use the "create from existing" method to set up the test DE.

• Primary key violation. If your DE has a primary key and more than one column is designated as the primary key, you may encounter a violation if there's already a record with the same key.

You can wrap your code in try-catch statements to capture exceptions, but this may not provide much useful information.

<script runat="server">
Platform.Load("core", "1");
try{

// Your Code

} catch (ex){
    Write(Stringify(ex));
}
</script>

Answer 2

Please take the time to read the official documentation on these two functions. The examples aren't terrible and you'll find the proper parameters spelled out.

• SSJS Data Extension Retrieve
• SSJS Data Extension Add

You'll need to assign a variable for the Add function results, since it returns the number of rows inserted.

I'd suggest testing in a CloudPage with debug flag along with a try/catch as @Duc Le suggested. Once it's working, set debug to false to disable the output.

<script runat="server" language="javascript">
   
 Platform.Load("core", "1");

 var debug = true;

 try {

   var de1 = DataExtension.Init("ExampleMaster");
  
   //var filter = {Property:"SubscriberKey",SimpleOperator:"equals",Value:"8b7df23e-dca3-4b69-be10-f07e43fd730d"};
   var filter;  
   // https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_dataExtensionRowsRetrieve.html 
   // limited to 2500 results
  
   var de1Results = de1.Rows.Retrieve(filter);

   if (debug) {
     Write("<hr>de1Results: " + Stringify(de1Results))
   }

   var de2 = DataExtension.Init("ExampleMasterCopy")

   // https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_dataExtensionAdd.html
   try {

     var de2Results = de2.Rows.Add(de1Results) 

     if (debug) {
       Write("<hr>de2Results: " + de2Results)
     }


   } catch (e2) {

     if (debug) {
       Write("<hr>e2: " + Stringify(e2))
     }

   }

 } catch (e) {

     if (debug) {
       Write("<hr>e: " + Stringify(e))
     }

 }

</script>

Results: