INSERT INTO — Marketing Cloud SQL reference | Cleon

INSERT INTO ... SELECT is the only way to write data in MC SQL. There's no standalone INSERT INTO target VALUES (...), no UPDATE target SET ..., no DELETE FROM target — every write is a SELECT that produces rows + a destination Data Extension that receives them, with merge behavior controlled by the SQL Activity's target action. Get the target action wrong and the same SELECT either rebuilds your DE cleanly, duplicates every row, or silently merges over data you wanted to keep.

Official syntax

You don't actually write the INSERT INTO clause yourself — Automation Studio wraps your SELECT in it based on the destination Data Extension you configured on the SQL Query Activity and the target action you picked. What you write:

-- This is the entire query body in the Activity:
SELECT
  SubscriberKey,
  EmailAddress,
  LoyaltyTier,
  LastPurchase
FROM master_subscribers
WHERE Status = 'Active';

-- What the Activity executes (conceptually):
--   <one of:>
--   1. TRUNCATE destination_de; INSERT INTO destination_de (...) <your SELECT>;   -- Overwrite
--   2. INSERT INTO destination_de (...) <your SELECT>;                            -- Append
--   3. MERGE destination_de USING (<your SELECT>) ON <PK match> ...;              -- Update

The three target actions (configured on the Activity, not in SQL):

| Action | What it does | Required setup | |---|---|---| | Overwrite | Clears the destination DE, then inserts every row from the SELECT | None — works on any DE | | Append | Inserts every row from the SELECT, on top of whatever's already there | None — but no dedup; duplicates accumulate | | Update | Matches against the destination DE's primary key. Existing rows: non-key columns updated. New rows: inserted. | Destination DE must have a primary key configured |

Column matching is by name (case-insensitive). The SELECT must produce columns whose names match the destination DE's columns. Extra columns in the SELECT are ignored; missing columns get the destination's default value (or NULL if no default).

Reference:

What survives in production

Pick the target action before writing the `SELECT`

The same SELECT produces wildly different outcomes depending on the action. Decide first.

-- This SELECT looks innocent
SELECT TOP 100 SubscriberKey, EmailAddress, LoyaltyTier
FROM master_subscribers
WHERE Status = 'Active'
ORDER BY LastPurchase DESC;

-- Under Overwrite: destination DE ends with exactly 100 rows. Sensible.
-- Under Append:    destination DE GROWS by 100 rows every run.
--                  After a week of daily runs, 700 rows, mostly duplicates.
-- Under Update:    if the destination has SubscriberKey as PK,
--                  100 rows get their non-key fields updated. The
--                  other ~9.9M rows in the destination stay as-is.

Same SQL. Three completely different production behaviors. Always answer "what happens to the rows that aren't in this SELECT?" before pressing save on the Activity.

`Update` mode requires a primary key — without one it silently behaves like `Append`

If you set the Activity to Update but the destination DE doesn't have a primary key configured, the merge can't happen — and instead of failing, it falls back to inserting every row. The behavior depends on the tenant; some treat it as an error, others as Append. Either way, your "incremental update" is now duplicating rows.

The defense: before saving an Update-mode Activity, open the destination DE's properties and confirm a primary key is set on the column(s) you intend to merge against. Usually SubscriberKey, sometimes a composite key.

`Overwrite` is "clear + insert" — and the clear is unconditional

Overwrite doesn't compare anything. It empties the destination DE, then inserts whatever the SELECT returns. If your SELECT returns zero rows (because of a bad JOIN, a WHERE that filtered too aggressively, a System Data View that emptied silently), the destination DE ends up empty — and the data that was there before is gone.

-- AT RISK — if _Sent rotation empties the join, destination DE
-- ends up with zero rows under Overwrite mode
SELECT s.SubscriberKey, s.EmailAddress
FROM master_subscribers s
INNER JOIN _Sent sent
  ON s.SubscriberKey = sent.SubscriberKey
WHERE sent.EventDate >= DATEADD(day, -30, GETDATE());

-- DEFENSIVE — verification step in a separate Activity that
-- guards against zero-row Overwrites
INSERT INTO de_stg_target
SELECT s.SubscriberKey, s.EmailAddress
FROM master_subscribers s
INNER JOIN de_log_sent_30d sent           -- snapshot, not _Sent directly
  ON s.SubscriberKey = sent.SubscriberKey;

-- Activity 2 only promotes if staging has rows above a threshold:
INSERT INTO destination_de
SELECT * FROM de_stg_target
WHERE EXISTS (
  SELECT 1 FROM de_stg_target
  GROUP BY 1
  HAVING COUNT(*) > 1000   -- whatever your floor is
);

The pattern: never Overwrite directly from a query that touches a System Data View. See FROM and gotchas — #6 for the SDV snapshot rule.

Match column names explicitly in the `SELECT`

SELECT * against a Data Extension that gets columns added later will start producing extra columns the destination DE doesn't have. Salesforce's behavior on extra columns is "silently ignore" — so your INSERT INTO succeeds, and a column that was supposed to land in the destination is missing without an error.

Always project explicitly:

-- AT RISK — if the source DE adds a new column tomorrow, this still
-- runs but the destination won't see the new value (column missing
-- in destination = silently ignored in the SELECT)
SELECT *
FROM master_subscribers;

-- DURABLE — explicit column list, breaks loudly if a column goes
-- away from the source instead of silently re-shaping the destination
SELECT
  SubscriberKey,
  EmailAddress,
  LoyaltyTier,
  LastPurchase
FROM master_subscribers;

See SELECT for the full rationale on avoiding SELECT *.

Length truncation is silent — match destination column lengths

If your SELECT projects an EmailAddress that's 75 chars long but the destination DE has EmailAddress VARCHAR(50), MC truncates to 50 chars on insert. No warning, no error. The destination ends up with 'german.medina@reallylongdomainn' instead of the full address — and downstream WHERE EmailAddress = 'german.medina@reallylongdomain.example.com' returns zero matches.

Defense: when the destination DE is created, size every string column to the source's MAX(LEN()) plus a safety margin. See gotchas — #5.

Quick decision

Use Overwrite when:

You're rebuilding the destination DE from scratch each run.
The SELECT is guaranteed to return a sensible row count (no SDVs, no fragile joins).
Stale data in the destination is worse than a moment of empty/partial data during the rewrite.

Use Append when:

The destination is an event log (sends, opens, errors) where every row is a new fact, not an update of an existing one.
You have downstream dedup if duplicates are not desired.
Combined with a de_log_runs row that tags which run wrote which batch.

Use Update when:

The destination DE has a stable primary key set.
You're merging incremental changes into a slowly-growing master DE.
The SELECT returns the new state of each row, not the diff.

Stage into de_stg_* first when:

The query touches a System Data View (always snapshot first).
The query is complex enough to produce zero rows on edge cases.
You want to verify row count before committing to the production DE.
The query is over 10 minutes against a representative production volume — split to stay under the 30-min timeout.

Basics — supported T-SQL subset and target action mechanics
SELECT — the projection that becomes the INSERT INTO ... SELECT
FROM — sources for the SELECT, including the SDV snapshot rule
JOIN — joining sources before insert
WHERE — filtering before insert
MC SQL gotchas — see #1 (no transactions), #2 (no UPDATE/DELETE), #3 (30-min timeout), #5 (length truncation), #7 (clear-DE folklore)

More reference pages incoming: String / Date / Numeric / Conversion / Aggregate / Null Functions · Style Guide.

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

Official syntax

What survives in production

Pick the target action before writing the SELECT

Update mode requires a primary key — without one it silently behaves like Append

Overwrite is "clear + insert" — and the clear is unconditional

Match column names explicitly in the SELECT