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. Copiying records from a Data Extension takes some time.


Have I missed anything?

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

Pay me a coffee

Want to say thanks? Pay me a coffee! Remember, I turn coffee into code.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

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