How to Create SQL Query Activities in Bulk Using SSJS in SFMC

Normally, creating SQL Query Activities in Salesforce Marketing Cloud is a one-and-done task — manual creation works just fine for a single use case. But what happens when you’re suddenly asked to generate 10, 100, or even 1,000 queries based on a segmentation logic, language split, or regional dataset?

That’s when the manual approach breaks down — and scripting comes to the rescue.

In this article, you’ll learn how to create SQL Query Activities in bulk using Server-Side JavaScript (SSJS) and the WSProxy library. This technique is especially useful when working with scalable automations, localized marketing, or massive DE structures where efficiency matters.

Before the Code: Naming Conventions & Structure Planning

Before we dive into the code, it’s crucial to establish naming standards for both the SQL Query Activities and their associated Data Extensions. Why? Because when you’re generating hundreds of definitions in bulk, consistent, predictable naming is the backbone of automation.

Depending on the scope of your deployment, your naming convention can be as simple or as structured as needed:

• If you operate across multiple markets or languages: Use a composite name that includes country and language e.g. COUNTRYLANGUAGE_SEGMENT_NAME
• If you operate in a single region/language setup: You can simplify the naming and omit the country-language prefix e.g. SEGMENT_NAME

This flexibility allows your loop logic to dynamically adjust the output based on how many markets you’re targeting. Just define your naming structure up front and keep it consistent — your future self will thank you.

To make the entire feature scalable and loop-friendly, you should be able to programmatically derive the CustomerKey of each Data Extension and Query Activity at any point in your code — without ever having to look it up manually.

Now that we’ve finalized our naming convention, we can generate the Data Extensions using a separate SSJS automation. While WSProxy works well for creating or deleting Data Extensions, it doesn’t reliably support updating an existing DE’s structure — especially when adding, removing, or modifying fields.

At least in my case, the update simply didn’t apply. And due to time constraints, I decided to purge all existing DEs and recreate them from scratch with the updated field definitions. This turned out to be the most reliable and efficient approach that time. Maybe I’ll come back to this topic and figure out why the update didn’t work for me.

Constructing SQL Query Definitions Dynamically

With our naming convention in place and the Data Extensions ready, the final step is to dynamically create the SQL Query Activities that populate those DEs with juicy segmentation data.

This is where the power of loops, conditions, and predictable naming really shines. Instead of manually building each activity, we loop through the list of segments (generated from country, audience, engagement, etc.) and programmatically construct:

• A Name for the query activity
• A CustomerKey based on a hash of the segment name
• A SQL QueryText with the correct WHERE clause filters
• A reference to the correct target Data Extension

Simple Setup for One Market

Now let’s take a look at the actual code and see how simple bulk creation of SQL Query Activities can be using SSJS. In this example, we’re working with a basic setup — no country or language splits, just a straightforward list of segments.

Each segment drives the creation of its own SQL activity and associated Data Extension. While this version is intentionally simple, your real-world use case can easily incorporate more advanced logic — including dynamic filters, joins, or audience rules — based entirely on the segment name.

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

// Declare all variables at the top
var api = new Script.Util.WSProxy(),
    result,
    status,
    qd,
    request,
    folderId = 11111,
    where,
    query,
    config,
    i,
    segments = ["SEGMENT_1", "SEGMENT_2", "SEGMENT_3", "SEGMENT_4", "SEGMENT_5"];

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

    query = "SELECT\nEmailAddress,\nLastName,\nSalutation,\nSegmentNumber\nFROM\nMasterDataExtension";
    where = [];

    if (segments[i].indexOf("_1") !== -1) {
        where.push("SegmentNumber = 1");
    } else if (segments[i].indexOf("_2") !== -1) {
        where.push("SegmentNumber = 2");
    } else if (segments[i].indexOf("_3") !== -1) {
        where.push("SegmentNumber = 3");
    } else if (segments[i].indexOf("_4") !== -1) {
        where.push("SegmentNumber = 4");
    }

    config = {
        CustomerKey: ("qd-" + Platform.Function.MD5(segments[i], 'UTF-16')).substring(0, 36),
        Name: segments[i],
        Description: "Waterfall segmentation for data extension " + segments[i],
        CategoryID: folderId,
        TargetType: "DE",
        TargetUpdateType: "Overwrite",
        QueryText: query + "\nWHERE\n" + where.join("\nAND "),
        DataExtensionTarget: {
            Name: segments[i],
            CustomerKey: ("de-" + Platform.Function.MD5(segments[i], 'UTF-16')).substring(0, 36)
        }
    };

    // Choose one of the following options:
    
    // Option 1: Create new Query Definition
    result = api.createItem("QueryDefinition", config);

    // Option 2: Update existing Query Definition
    /*
    qd = QueryDefinition.Init(("qd-" + Platform.Function.MD5(segments[i], 'UTF-16')).substring(0, 36));
    status = qd.Update({
        QueryText: query + "\nWHERE\n" + where.join("\nAND ")
    });
    */

    // Optional logging for cloud page
    // Write("Processed: " + segments[i] + " — Status: " + result.Status + "<br>");
}
</script>

This script programmatically creates SQL Query Activities in Salesforce Marketing Cloud using Server-Side JavaScript (SSJS) and the WSProxy API. It loops through a predefined list of segments, dynamically builds SQL queries based on each segment’s name, and then creates (or optionally updates) a corresponding QueryDefinition object.

I like to start with all variable declarations at the top. Additionally, we define a simple array of segment identifiers, such as "SEGMENT_1", "SEGMENT_2", and so on. These represent logical audience splits or filtering criteria. The loop then iterates over each segment and, based on the number in the name, constructs a WHERE clause to filter on a specific SegmentNumber.

For example, "SEGMENT_1" results in a SQL filter of SegmentNumber = 1. This is just a simplified example — in a real-life scenario, the logic can be much more complex depending on your segmentation requirements.

Multi-Country Configuration

When working across multiple countries or language regions, a flat segmentation approach isn’t enough. In this setup, we introduce country-specific logic into our loop, allowing each SQL Query Activity to be tailored based on both country and language codes.

By dynamically combining region codes with segment identifiers, we can generate localized queries that target the right audience in each market. This configuration is essential for global brands or multi-regional campaigns where data structures or business logic vary by country.

Please leave this field empty

Oh hey there
I’ve got something special for you—free scripts every month!

Sign up now to receive monthly premium Salesforce Marketing Cloud script examples straight to your inbox.

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.

Share With Others