Can we run Dynamic SQL in Marketing cloud

Question

I have the following tables

DE1

ID DE_Name Product
1 Sample_1 PRD1
1 Sample_2 PRD2
1 Sample_4 PRD4
2 Sample_3 PRD3

Basically column TableName contains actual Table Name that needs to be queried

Sample_1 (DE Name)

Dept_no
123
456
789

Sample_2 (DE Name)

Dept_no
125
457

Sample_3 (DE Name)

Dept_no
025
0157
0010
12456

Sample_4 (DE Name)

Dept_no
12345
457
79000
98345

Desired Output (Into a New DE)

Table 2

ID Dept_no Product
1   123     PRD1
1   456     PRD1
1   789     PRD1
1   125     PRD2
1   457     PRD2
1   12345   PRD4
1   457     PRD4
1   79000   PRD4
1   98345   PRD4
2   025     PRD3
2   0157    PRD3
2   0010    PRD3
2   12456   PRD3

Can you please let me know if we can achieve desired output DE set through SQL or do we need SSJS.

I tried the scripting in JS and it worked fine except that salesforce has limiation to extract only 2500 rows per retrive from a DE

I am thinking of using cursors but SFMC SQL doesnt seem to understand cursors.

can anyone please provide any insights on how to get data into Table2 format?

Answer 1

You can't use procedural T-SQL in query activities and retrieving rows like that with SSJS won't scale too well. You can use SSJS to manipulate your SQL, though.

Create your results data extension and an automation containing an SSJS activity and a SQL query activity. Use the SSJS activity to define the the text of your query before it's executed. In your case, you're building a query with a bunch of UNION ALLs.

The body of your SSJS activity would look something like this:

    <script runat="server" language="JavaScript">
        Platform.Load("core","1");
        // instantiate the WSProxy object to make API calls
        var api = new Script.Util.WSProxy();
    
        // Retrieve DE1 rows
        var de1 = DataExtension.Init("DE1");
        var tables = de1.Rows.Retrieve();
        
        var sql = "";
        var targetKey = 'the_external_key_of_results_de';
        var targetName = 'the_name_of_results_de';
        var queryKey = 'the_external_key_of_query_activity';
        var objectId = 'the_objectid_of_query_activity';
    
        // Build UNION ALL SQL
        for (var i = 0; i < tables.length; i++) {
            var table = tables[i];
            sql += "SELECT '" + table.ID + "' AS ID, Dept_no, '" + table.Product + "' AS Product\n";
            sql += "FROM " + table.DE_Name + "\n";
            if (i+1 != tables.length) sql += "UNION ALL\n"
        }
    
        // Define query definition to update by API
        var qdf = {
            "CustomerKey": queryKey,
            "ObjectID": objectId,
            "QueryText": sql,
            "TargetType": "DE",
            "TargetUpdateType": "Overwrite",
            "DataExtensionTarget": {
                "CustomerKey": targetKey,
                "Name": targetName
            }
        };
    
        // Update the query def
        var options = {};
        var response = api.updateItem("QueryDefinition",qdf,options);
        Write(Stringify(response));
    </script>