Basics — Marketing Cloud SQL fundamentals | Cleon

SQL in Marketing Cloud is what you reach for when Filter Activities and Drag-and-Drop Segmentation can't do what the business needs. It lives in Automation Studio, runs as a SQL Query Activity, and writes its results to a Data Extension you point it at. That's the full surface — there's no ad-hoc SQL console, no psql, no backup tooling.

This page is the orientation. The rest of the SQL catalog (clauses, functions, style) builds on this mental model.

Official syntax

Every SQL Query Activity has the same shape: a SELECT (with optional FROM, JOIN, WHERE, GROUP BY, etc.) that produces rows, plus a configured destination Data Extension that receives them. You don't write the INSERT INTO — Automation Studio writes it for you, based on the destination DE you picked and the target action (Overwrite, Append, Update) you configured.

-- This is the entire query you write in the Activity:
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.LoyaltyTier
FROM master_subscribers s
WHERE s.Status = 'Active';

-- The Activity wraps it as:
--   INSERT INTO <destination_de>
--   <your SELECT above>
-- with merge behavior defined by the Activity's target action.

The supported syntax is a strict subset of T-SQL:

SELECT projection with column aliases, expressions, and column-level functions ✓
FROM Data Extensions and System Data Views ✓
WHERE, GROUP BY, HAVING, ORDER BY (with caveats) ✓
INNER, LEFT, RIGHT, FULL OUTER joins ✓
CASE, LIKE, IN, IS NULL ✓
Common functions: COUNT, SUM, AVG, MIN, MAX, DATEADD, DATEDIFF, LEN, LEFT, RIGHT, LOWER, UPPER, REPLACE, CAST, CONVERT, ISNULL, COALESCE ✓
INSERT, UPDATE, DELETE, MERGE as standalone statements ✗ — only INSERT INTO ... SELECT via the Activity wrapper
CREATE, ALTER, DROP ✗
Stored procedures, triggers, views ✗
Transactions (BEGIN TRAN / COMMIT / ROLLBACK) ✗
Window functions, CTEs — edition-dependent (see gotchas — #4)

Reference:

What survives in production

The destination DE shapes everything

The SELECT you write doesn't define the destination. The destination Data Extension does — column names, types, lengths, primary key, retention. Your SELECT produces rows; the destination decides what fits and what gets truncated, dropped, or merged.

[ SELECT ] → rows (untyped projection)
        ↓
[ Destination DE ]
   - Column names must match (case-insensitive)
   - Types are coerced to destination types
   - Lengths truncate silently if SELECT is wider
   - Primary key drives Update mode merge
   - Retention setting drives auto-deletion

If the names in your SELECT don't match the destination DE columns, the Activity fails loudly. If the types or lengths don't match, it succeeds and corrupts your data quietly. See gotchas — #5.

Pick the target action before you write the query

The Activity has three target actions; pick before you write a line of SQL because each implies a different query shape:

Overwrite — destination DE is cleared, then rows from the SELECT are inserted. Use for full rebuilds.
Append — rows from the SELECT are added to whatever is already there. No deduplication; duplicates are your problem.
Update — rows match against the destination DE's primary key and merge in. Existing rows get their non-key columns overwritten by the SELECT's values; new rows are inserted. Requires the destination DE to have a primary key set.

A SELECT TOP 100 makes sense in Overwrite (you really want exactly those 100 rows in the destination); the same query in Append leaves you with 100 new duplicates each run. Same SQL, opposite outcomes.

Where SQL Activities live and how they run

SQL Query Activities don't exist in isolation — they live inside an Automation in Automation Studio. An Automation can be:

Scheduled — runs at a fixed cadence (every hour, every Tuesday, etc.).
Triggered — runs when a file lands in an Enhanced FTP folder (file drop trigger).
Run once — manually kicked off, useful for migrations and one-shot fixes.

A single Automation can chain multiple Activities — typically: SQL Query → Verification → Send Email → Log to DE. Each Activity is independent; if step 2 fails, step 1's writes are still committed (no transactions, see gotchas — #1).

Quick decision

Reach for SQL Query Activity when:

You need to combine data from multiple Data Extensions (joins).
You need to transform values (case logic, date math, string normalization).
You need to deduplicate, segment, or aggregate data into a destination DE.
The query benefits from being scheduled or triggered (not ad-hoc).

Reach for a Filter Activity instead when:

You only need a WHERE against a single Data Extension and the destination is also a single DE.
The audience is small enough that the visual builder is faster than writing SQL.
Non-technical stakeholders need to maintain it.

Reach for AMPscript or SSJS instead when:

The transformation is per-message (personalization at send time).
The data lives outside MC and needs a callout to retrieve.
You need control flow that SQL doesn't express (loops, conditional sends).

Don't reach for SQL Query Activity when:

You actually want to delete or alter rows in place — MC SQL can't.
You need a transaction across multiple writes — MC SQL can't.
You're under a 30-minute window with multi-million-row joins on a busy tenant — split into staged Activities first.

SELECT — the entry clause every query starts with
MC SQL gotchas — what fires at scale once you're past the basics

More reference pages incoming: FROM · JOIN · WHERE · LIKE · CASE · INSERT INTO · String / Date / Numeric / Conversion / Aggregate / Null Functions · Style Guide.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.