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.