INSERT

On this page

Adds rows from a query to a table. The input can be literal rows from VALUES or the result of another query.

Syntax

<query> INSERT INTO <table_name> [ ( <col_name> [, ...] ) ]

Column mapping

Input columns map by position, not by their output names:

  • Without a column list, values follow the table’s ordinary column order, skipping computed columns. Missing trailing ordinary columns are filled with NULL. Extra values cause an error.
  • With a column list, values follow the listed order. The number of input columns must match the list; unlisted ordinary columns are filled with NULL. Computed columns cannot be write targets.
  • Duplicate or unknown names in the column list cause an error.

Examples

Insert event records

Create a table for a small batch of checkout events:

CREATE TABLE checkout_import (
    event_id string,
    event string,
    campaign string
)

Insert two events without campaign values. The omitted trailing column becomes NULL:

VALUES ('evt-17', 'checkout_started'), ('evt-42', 'payment_failed')
INSERT INTO checkout_import

Supply an explicit column list when the input uses a different order:

VALUES ('spring', 'evt-63', 'payment_completed')
INSERT INTO checkout_import (campaign, event_id, event)

Read the inserted rows:

FROM checkout_import
ORDER BY event_id
+----------+-------------------+----------+
| event_id | event             | campaign |
+----------+-------------------+----------+
| evt-17   | checkout_started  | NULL     |
| evt-42   | payment_failed    | NULL     |
| evt-63   | payment_completed | spring   |
+----------+-------------------+----------+

Insert query results

Create a table of event counts:

CREATE TABLE checkout_counts (event string, events int)

Aggregate the imported batch and insert the result:

FROM checkout_import
GROUP BY event
AGGREGATE count() AS events
INSERT INTO checkout_counts

Each execution inserts another set of rows; it does not replace an existing summary.