How to run SQL Query Activity in Automation Studio
SQL Query Activity in Salesforce Marketing Cloud (SFMC) allows you to query Data Extensions (DEs) using SQL and store results in another DE. This is useful for filtering, segmenting, and transforming data.
Steps to Create and Run an SQL Query Activity in Automation Studio
- Navigate to Automation Studio
- Log in to Salesforce Marketing Cloud.
- Go to Email Studio → Automation Studio.
- Click on the “Activities” tab.
- Create a New SQL Query Activity
- Click “Create Activity” → Select “SQL Query”.
- Give your query a Name and an optional Description.
- Click “Next”.
- Write and Test Your SQL Query
- In the Query Editor, write your SQL query.
- Ensure your SQL follows SFMC SQL syntax (based on Microsoft SQL Server.
Example Query:
Get all Student_Records where city is Bangalore, store them in BangaloreStudent_DE.
Select Student_Name,Age,City from Students_Record where City=’Bangalore’
- Select a Target Data Extension
- Create or Select an Existing Data Extension where the query results will be stored.
- Choose Append, Update, or Overwrite based on how you want the data handled:
Action | Behavior |
Append | Adds new records without deleting existing ones |
Update | Updates records where keys match, adds new ones if no match |
Overwrite | Deletes all existing data and inserts new query results |
- Click “Next”
- Click “Finish” to save the SQL Query Activity.
- Run it manually or use Automation Studio to schedule it.
Running SQL Query in an Automation Workflow
To automate SQL execution:
- Go to Automation Studio → Automations.
- Click “Create Automation” → Select “Schedule” or “Triggered”.
- Click “Add Step” → Choose “SQL Query” Activity.
- Select your SQL Query Activity from the list.
- (Optional) Add additional activities like Data Extract, File Transfer, or Email Send.
- Save and activate the automation.
Best Practices for Running SQL Queries in SFMC
- Use TOP or LIMIT to prevent large data pulls that may slow performance.
- Ensure Data Extension fields match your query output to avoid errors.
- Avoid SELECT *, explicitly mention the fields for better performance.
- Test in Query Studio before running in Automation Studio to catch error
