Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.narrative.io/llms.txt

Use this file to discover all available pages before exploring further.

The INSERT statement adds rows to an existing dataset. NQL supports two forms: INSERT ... VALUES (...) for literal rows, and INSERT ... SELECT ... for inserting the result of a query.
INSERT runs against datasets you own. To create a new dataset from a query, use CREATE MATERIALIZED VIEW. To upsert into an existing materialized view during refresh, use the MERGE ON clause.

​
Syntax

​
VALUES form

INSERT INTO table_reference (column1, column2, ...)
VALUES
  (value1, value2, ...)
  [, (value1, value2, ...), ...]

​
SELECT form

INSERT INTO table_reference (column1, column2, ...)
SELECT
  column1,
  column2,
  ...
FROM source_reference
[WHERE condition]

​
Explicit column list is required

The column list after INSERT INTO table_reference is required. NQL does not infer columns from the dataset schema, and wildcards (SELECT *) are not supported in the SELECT form either. 
-- Correct
INSERT INTO company_data."123" (user_id, email)
VALUES (42, '[email protected]')

-- Not supported — missing column list
INSERT INTO company_data."123"
VALUES (42, '[email protected]')
See Explicit column selection for the rationale and how it applies across NQL.

​
Source restrictions for the SELECT form

When you use INSERT ... SELECT, the source query can read only from datasets and views your company owns. The following sources are rejected:
  • Datasets owned by another company (including those shared with you through access rules)
  • Access rules (e.g., provider_slug."access_rule_name")
  • narrative.rosetta_stone
If you need to ingest data from a shared access rule, materialize it first with CREATE MATERIALIZED VIEW, then INSERT ... SELECT from your materialized view. Subqueries inside the SELECT — including joins, CTEs, and IN (SELECT ...) predicates — are subject to the same restriction.

​
Column matching and types

  • The number of expressions in each VALUES row (or columns in the SELECT clause) must match the column list.
  • Expressions are matched to columns by position, not by name.
  • Each expression must be assignable to the target column’s data type. Implicit coercions follow the same rules as comparisons elsewhere in NQL.

​
Examples

Single-row VALUES: 
INSERT INTO company_data."123" (dataset_id, status, created_at)
VALUES (456, 'created', CURRENT_TIMESTAMP)
Multi-row VALUES: 
INSERT INTO company_data.pipeline_log (dataset_id, status, created_at)
VALUES
  (101, 'created', CURRENT_TIMESTAMP),
  (102, 'created', CURRENT_TIMESTAMP),
  (103, 'skipped', CURRENT_TIMESTAMP)
INSERT from a SELECT: 
INSERT INTO company_data."456" (user_id, email, created_at)
SELECT
  company_data."123".user_id,
  company_data."123".email,
  company_data."123".created_at
FROM company_data."123"
WHERE company_data."123".created_at >= CURRENT_DATE - INTERVAL '7' DAY
INSERT with transformations and a join: 
INSERT INTO company_data."789" (user_id, email, region)
SELECT
  u.user_id,
  LOWER(u.email) AS email,
  g.region
FROM company_data."123" AS u
JOIN company_data."456" AS g
  ON u.user_id = g.user_id
WHERE u.is_active = TRUE

​
Execution context

INSERT statements can run in two places:
Execution surfaceWhen to use
NQL query endpointAd-hoc inserts from an API client or Data Studio
ExecuteDml workflow taskScheduled or event-driven inserts inside a workflow
INSERT does not create datasets. The target dataset must already exist. Use CREATE MATERIALIZED VIEW to create datasets from a query, then INSERT to add rows afterward.
For bulk loads from one dataset to another, INSERT ... SELECT is almost always faster and cheaper than issuing many single-row INSERT ... VALUES statements.

​
Related content

UPDATE

Modify existing rows in a dataset

DELETE

Remove rows from a dataset

Incremental Upserts with MERGE ON

Update or insert rows during materialized view refresh

CREATE MATERIALIZED VIEW

Create a new dataset from a query

ExecuteDml workflow task

Run DML statements from a workflow

Data Types

Type rules for INSERT expressions