SQL
Insert

INSERT Statement

<sql> ::= <insert_statement>

<insert_statement> ::= "INSERT INTO" <table_name> "(" <column_list> ")" <insert_values>
<insert_values> ::= "VALUES" "(" <value_list> ")" | <query>

<query> ::= "SELECT" <select_list> "FROM" <from_item> ["JOIN" <join_clause>] ["WHERE" <condition>] ["ORDER BY" <order_clause>] ["TYPE" <data_type>]

<select_statement> ::= "SELECT" <column_list> "FROM" <table_name> ["WHERE" <condition>]

<column_list> ::= <column_name> ["," <column_name>]*
<value_list> ::= <value> ["," <value>]*
<set_clause> ::= <column_name> "=" <value> ["," <column_name> "=" <value>]*

<condition> ::= <expression> ["AND" <expression>]*
<expression> ::= <column_name> <operator> <value>
<operator> ::= "=" | "<>" | "<" | ">" | "<=" | ">=" | "IS NOT NULL"

<table_name> ::= [a-zA-Z_][a-zA-Z0-9_.]*
<column_name> ::= [a-zA-Z_][a-zA-Z0-9_]*
<value> ::= <literal> | <parameter>
<literal> ::= '"[^"]*"' | "'[^']*'" | [0-9]+
<parameter> ::= "&" [a-zA-Z_][a-zA-Z0-9_]*
<from_item> ::= <table_name>
<join_clause> ::= <table_name> ["ON" <condition>]
<order_clause> ::= <column_name> ["ASC" | "DESC"]
<data_type> ::= "OBJECT" | "LIST"

Explanation

  1. SQL Statement Types (<sql>): The BNF defines two primary types of SQL statements - INSERT and SELECT.

  2. INSERT Statement (<insert_statement>): An INSERT statement starts with INSERT INTO, followed by a table name, a list of columns, and the values to be inserted. Values can either be directly specified (VALUES) or derived from a SELECT query (<query>).

  3. Query (<query>): Represents a SELECT statement used as part of an INSERT operation. The query may include joins, conditions, ordering, and a data type specification.

  4. Components:

    • Column List (<column_list>): Defines one or more columns to be used in the statement.
    • Value List (<value_list>): Defines one or more values to be used in the VALUES clause.
    • Set Clause (<set_clause>): Used in UPDATE statements to specify column-value assignments.
    • Condition (<condition>): Specifies filtering conditions for WHERE clauses.
    • Expression (<expression>): Represents a conditional expression that compares a column with a value using an operator.
    • Operators (<operator>): Defines the comparison operators, such as =, <>, <, >, <=, >=, and IS NOT NULL.
    • Join Clause (<join_clause>): Represents a table join, optionally specifying a condition with ON.
    • Order Clause (<order_clause>): Specifies the columns to order the results by, with optional sorting direction (ASC or DESC).
    • Data Type (<data_type>): Specifies the type of the result, such as OBJECT or LIST.

  5. Table and Column Naming:

    • Table Name (<table_name>): Represents the name of the table, following typical SQL naming conventions.
    • Column Name (<column_name>): Represents the name of a column, following typical SQL naming conventions.

  6. Values:

    • Literal (<literal>): Represents a value such as a string or number.
    • Parameter (<parameter>): Represents a parameter that starts with &.

Example 1

INSERT INTO dictionary.users(ref, id, name, name1, name2, name3)
SELECT uuid(x.id), x.id, x.name AS name, x.name AS name1, x.name AS name2, x.name AS name3
FROM &params AS x(id int, name text, name1 text, name2 text, name3 text)
ON CONFLICT(ref) DO UPDATE SET name = x.name, id = EXCLUDED.id;

Example 2

INSERT INTO Dictionary.syncevents("from", "to", "in", "Out")
SELECT &returnValues.sender, &returnValues.to, &returnValues.amount1In, &returnValues.amount0Out
WHERE &returnValues.amount1In IS NOT NULL
TYPE OBJECT;

Example 3

INSERT INTO Operation.operation_order(price, value, storage, goods)
SELECT 10 AS price, 22 AS val,
  (SELECT st.ref
   FROM Dictionary.storages AS st
   WHERE st.name = "main"
   LIMIT 1) AS storage,
  (SELECT gd.ref
   FROM Dictionary.goods AS gd
   WHERE gd.name = "Teapot 1"
   LIMIT 1) AS goods
TYPE LIST;
SelectUpdate