Ampscript or SSJS method to copy data extension

Question

Is there a way to copy a data extension, either using ampscript or SSJS, to create a new data extension? Some of our work involves duplicating several data extensions with similar schema and it would be helpful to be able to do this programatically.

I've started with a landing page with a form where people enter the name of the DE they want to copy, and a new name for the new one. That is as far as I've got. I'm passing these 2 values through to the next page on submission, but don't really know how to proceed from here.

I have previously written ampscript to create a DE using a specific schema, but am unsure how to copy the entirety of an existing one.

Please note I want structure only, not data too.

Answer 1

---

EDIT

So I figured out that the object we need is the DataExtensionField object - which is likely where the issue was coming in. See below for a working version that has successfully copied a DE:

<script runat="server">

/******* Retrieves Fields *******/

var prox2 = new Script.Util.WSProxy();
var cols2 = ["Client.ID","CustomerKey","DefaultValue","FieldType","IsPrimaryKey","IsRequired","MaxLength","ModifiedDate","Name","ObjectID","Ordinal","Scale"];
var filter2 = {
    Property: "DataExtension.CustomerKey",
    SimpleOperator: "equals",
    Value: "AMPScriptTest"
};
var fields = prox2.retrieve("DataExtensionField", cols2, filter2); /* WSProxy Retrieve */

/* Results of the Retreive */
var fieldsResults = fields.Results;

var i;
var fieldsFinal = [] 

/*******  For loop to weed out some of the troublesome props in Results Object  ******/

for (i=0;i<fieldsResults.length;i++) {

        var fieldsRow = fields.Results[i];

        delete fieldsRow.ObjectID;
        delete fieldsRow.StorageType;

        if (fieldsRow.FieldType != "Text") {
             delete fieldsRow.MaxLength;
        }

        delete fieldsRow.DataExtension;
        delete fieldsRow.PartnerMap;
        delete fieldsRow.AttributeMaps;
        delete fieldsRow.Markups;
        delete fieldsRow.Precision;
        delete fieldsRow.Scale;
        delete fieldsRow.PicklistItems;
        delete fieldsRow.References;
        delete fieldsRow.Client;
        delete fieldsRow.PartnerKey;
        delete fieldsRow.PartnerProperties;
        delete fieldsRow.CreatedDate;
        delete fieldsRow.ModifiedDate;
        delete fieldsRow.ID;

    fieldsFinal.push(fieldsRow);
} 




/* Create DE */
var proxCreate = new Script.Util.WSProxy();

var customerKey = Platform.Function.GUID(); /* Replace with CustomerKey */
var name = "myDE"; /* Replace with your Name */
var description = "This is a test DE made in WSProxy" /* Replace with your Description */
var folder = 12345 /* This is the ID of the folder you want your DE placed in */

var de = {
    Name: name,
    CustomerKey: cusstomerKey,
    Description: description,
    Fields: fieldsFinal,
    CategoryID: folder
}

var create = proxCreate.createItem("DataExtension", de);

</script>

Please note I had to delete some of the properties in the field objects in order to push it through. Feel free to fiddle around with this to optimize it as best you can. I was limited on time so I didn't spend long on it.

---

There is this capability inside the SDK, otherwise the best method I found was utilizing the SOAP API.

SSJS has some functions that deceive you into thinking it is possible, but the DataExtension.Retrieve() function results come in a completely different format than what is needed in DataExtension.Add()

My recommendation would be to use WSProxy() Retrieve then parse those results and utilize a WSProxy() Create to build the duplicate DE.

Something like below:

Retrieve

var prox = new Script.Util.WSProxy();
var cols = ["Name","CustomerKey","CategoryID","IsSendable"];
var filter = {
    Property: "CustomerKey",
    SimpleOperator: "equals",
    Value: "ArtistsDE"
};
var desc = prox.retrieve("DataExtension", cols, filter);

Create

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

var guid = Platform.Function.GUID();
var name = "my test de - " + guid;

var de = {
    Name: name,
    CustomerKey: guid,
    Description: "Another DE added via SSJS",
    Fields: [{
        FieldType: "Text",
        Name: "ID",
        MaxLength: 36,
        IsPrimaryKey: true,
        IsNillable: false,
        IsRequired: true
    },
    {
        FieldType: "Text",
        Name: "Name",
        MaxLength: 200
    }],
    CategoryID: 101377
}

var res = prox.createItem("DataExtension", de);

Another possible solution is to create a DE template (believe you need support to assist) and just create utilizing this template to duplicate DE set up.