How to clone a Data Extension and its records with server-side JavaScript

How to clone a Data Extension and its records with server-side JavaScript

This article explains how to clone a Data Extension and its records with server-side Javascript in Salesforce Marketing Cloud.

Not a fan of reading? Jump to the code snippet.

Save time with SSJS

In Marketing Cloud, we can easily copy a Data Extension from the Content Builder interface.

But if we wish to copy the records as well, we need to perform an export/import operation of a .csv file, which may take some time and create errors.

With server-side Javascript we can automate this process and save us the hastle of using the interface.

Build a function

For the purpose of this exercice, we are going to build a function that does all the work and simply call it by providing the name of the Data Extension to clone and the name of the target Folder.

Retrieve the Folder

This operation is explained on my previous aricle and returns the CategoryID parameter used for the creation of a new Data Extension.

<script runat="server">
    Platform.Load("core", "1.1");
	var req1 = Folder.Retrieve({Property:'Name',SimpleOperator:'equals',Value:'MyFolder'});
    var FolderID = req1[0].ID;
</script>	

Retrieve the fields

In order to retrieve the fields from a Data Extension, we need to use the CustomerKey and NOT THE NAME of the Data Extension. Don’t believe everything you read in the official documentation.

We can use this method to retrieve the CustomerKey based on the name of the Data Extension.

<script runat="server">
    Platform.Load("core", "1.1");
	var req2 = DataExtension.Retrieve({Property:"Name",SimpleOperator:"equals",Value:'MyDataExtension'});
    var CustomerKey = req2[0].CustomerKey;
	
	var DE = DataExtension.Init(CustomerKey);
    var fields = DE.Fields.Retrieve();
</script>

Unfortunately, the fields we are retrieving cannot be provided to the DataExtension object just yet.

We need to add the property IsRequired to every PrimaryKey field.

for(var i = 0; i < fields.length; i++) {
	if(fields[i]['IsPrimaryKey'] == true ) fields[i]['IsRequired'] = true;
}

Create the Data Extension

Now it’s time to create the Data Extension and in order to do so, we are going to use WSProxy, as it returns better error messages.

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

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

	var req1 = Folder.Retrieve({Property:'Name',SimpleOperator:'equals',Value:'MyFolder'});
	var FolderID = req1[0].ID;

	var req2 = DataExtension.Retrieve({Property:"Name",SimpleOperator:"equals",Value:'MyDataExtension'});
	var CustomerKey = req2[0].CustomerKey;
	
	var DE = DataExtension.Init(CustomerKey);
	var fields = DE.Fields.Retrieve();

    for(var i = 0; i < fields.length; i++) {
        if(fields[i]['IsPrimaryKey'] == true ) fields[i]['IsRequired'] = true;
    }

	var NewCustomerKey = Platform.Function.GUID();
	
	var ClonedDE = {
		'CustomerKey' : NewCustomerKey,
		'Name' : 'MyDataExtension clone',
		'CategoryID': FolderID,
		'Fields' : fields
	}

    var res = prox.createItem("DataExtension", ClonedDE);
    var message = res.Results[0].StatusCode + ": " + res.Results[0].StatusMessage;
	
    Write(message);
</script>

Retrieve the records

In order to retrieve every record from our Data Extension, we need to use a trick which consists of using a complex filter on the first field and returns every record.

var filter = {
	LeftOperand: { Property:fields[0]['Name'],SimpleOperator:"isNotNull" },
	LogicalOperator: "OR",
	RightOperand: { Property:fields[0]['Name'],SimpleOperator:"isNull" }
}

var records = DE.Rows.Retrieve(filter);

Then, let the DataExtension.Rows.Add function do the rest. Just provide it with the CustomerKey that we generated for the new Data Extension.

var DE2 = DataExtension.Init(NewCustomerKey);
var res2 = DE2.Rows.Add(records);           

Full code

Let’s compile everything into a function and return the error or success messages.

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

    var resp = cloneDataExtension('MyDataExtension','MyFolder');
    Write(resp);

    function cloneDataExtension(extName,folderName) {

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

        var req1 = Folder.Retrieve({Property:'Name',SimpleOperator:'equals',Value:folderName});
        var FolderID = req1[0].ID;

        var req2 = DataExtension.Retrieve({Property:"Name",SimpleOperator:"equals",Value:extName});
        var CustomerKey = req2[0].CustomerKey;

        var DE = DataExtension.Init(CustomerKey);
        var fields = DE.Fields.Retrieve();
        
        var filter = {
            LeftOperand: { Property:fields[0]['Name'],SimpleOperator:"isNotNull" },
            LogicalOperator: "OR",
            RightOperand: { Property:fields[0]['Name'],SimpleOperator:"isNull" }
        }

        var records = DE.Rows.Retrieve(filter);

        for(var i = 0; i < fields.length; i++) {
            if(fields[i]['IsPrimaryKey'] == true ) fields[i]['IsRequired'] = true;
        }

        var NewCustomerKey = Platform.Function.GUID();

        var ClonedDE = {
            'CustomerKey' : NewCustomerKey,
            'Name' : extName + ' clone',
            'CategoryID': FolderID,
            'Fields' : fields
        }

        var res = prox.createItem("DataExtension", ClonedDE);
        var message = res.Results[0].StatusCode + ": " + res.Results[0].StatusMessage;

        try {
            var DE2 = DataExtension.Init(NewCustomerKey);
            var res2 = DE2.Rows.Add(records);
            message += "Records added: " + res2;
        } catch(err) {
            message += "No records added";
        }
		
        return message;
    }
</script>

Troubleshooting

Copying the Data Extension is quet fool proof, but when it comes to copying the records, the trick that we implemented might not work if the first field (with Ordinal : 0) has a type that can’t be Null or NotNull.

To bypass this limitation, we can specify a field name instead.

var filter = {
	LeftOperand: { Property:"SubscriberKey",SimpleOperator:"isNotNull" },
	LogicalOperator: "OR",
	RightOperand: { Property:"SubscriberKey",SimpleOperator:"isNull" }
}

Considerations

Please be patient. Copying records from a Data Extension takes some time.

Please be aware that the Rows.Retrieve() method can only retrieve 2500 records per request. This means that only the first 2500 records will be copied from the original Data Extension. This is a Marketing Cloud limitation and I’m currently looking into how to bypass it.

Have I missed anything?

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

  1. Thanks for the great article Ivan.
    What’s the advantage of using this approach over using SQL?

  2. I had a similar need recently. I ended up dynamically creating a SQL query definition, then starting it with SSJS after I duplicated the DE.

    Don’t know if it is the most elegant of solutions and creates additional assets but only way I’ve seen to get past the 2,500 records.

  3. Thank you for the code. The subject of cloning inspired me to overcome my persistent challenge with filtering DE for multiple countries. I did not find a suitable and elegant solution to automate filtering – therefore I used your cloning method with a modified filter attributes. I literally clone my main DE into ‘filtered’ DEs as I need them. Really good stuff. Thanks for sharing

  4. This is exactly what I needed thanks! Although there’s one thing that’s kind of odd, the fields are scrambled in a random order it seems. Any tips to keep the field order te same for the copied DE?

    Thanks!

  5. Arthur, there is indeed a way, just add this on the line 14:

    fields.sort(function (a, b) { return (a.Ordinal > b.Ordinal) ? 1 : -1 } );
    
  6. There is one problem in creating a DE in a particular folder. When the clone DE folder name (or ID) is provided, we get this error – “Error: Unable to save data extension into a non-data extension category. The category provided was of type automations.”.

    Work around – create the folder in ‘Data Extensions’ folder (root folder) and then move them.

  7. Matheswaran, this could happen if you have 2 or more folders with different types but the same name. The best way to avoid this kind of issue is to use a complex filter when retrieving the folder:

    var result = Folder.Retrieve({
        LeftOperand: {
            Property: 'Name',
            SimpleOperator: 'equals',
            Value: 'MyFolder'
        },
        LogicalOperator: "AND",
        RightOperand: {
            Property: 'ContentType',
            SimpleOperator: 'equals',
            Value: 'dataextension'
        }
    });
    

Comments are closed.

Salesforce Marketing Cloud
Up Next:

How to send an HTTP request from one Marketing Cloud page to another

How to send an HTTP request from one Marketing Cloud page to another