## Synopsis Use the `INSERT` statement to add a row to a specified table. ## Syntax ### Diagram INSERTINTOtable_name(,column_name)VALUES(,expression)IFNOTEXISTSif_expressionUSINGusing_expressionRETURNS STATUS AS ROW ### using_expression ```ebnf using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression }; ``` ANDttl_or_timestamp_expression ### ttl_or_timestamp_expression ```ebnf ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression; ``` TTLttl_expressionTIMESTAMPtimestamp_expression ### Grammar ```ebnf insert ::= INSERT INTO table_name ( column_name [ , ... ] ) VALUES ( expression [ , ... ] ) [ IF { [ NOT ] EXISTS | if_expression } ] [ USING using_expression ] [ RETURNS STATUS AS ROW ] ``` Where - `table_name` and `column` are identifiers (`table_name` may be qualified with a keyspace name). - `value` can be any expression although Apache Cassandra requires that `value`s must be literals. - Restrictions for `if_expression` and `ttl_expression` are covered in the Semantics section. - See [Expressions](..#expressions) for more information on syntax rules. ## Semantics - An error is raised if the specified `table_name` does not exist. - The columns list must include all primary key columns. - The `USING TIMESTAMP` clause indicates you would like to perform the INSERT as if it was done at the timestamp provided by the user. The timestamp is the number of microseconds since epoch. - By default `INSERT` has `upsert` semantics, that is, if the row already exists, it behaves like an `UPDATE`. If pure `INSERT` semantics is desired then the `IF NOT EXISTS` clause can be used to make sure an existing row is not overwritten by the `INSERT`. - **Note**: You should either use the `USING TIMESTAMP` clause in all of your statements or none of them. Using a mix of statements where some have `USING TIMESTAMP` and others do not will lead to very confusing results. - Inserting rows with TTL is not supported on tables with [transactions enabled](./../ddl_create_table#table-properties-1). - `INSERT` is always done at `QUORUM` consistency level irrespective of setting. ### `VALUES` clause - The values list must have the same length as the columns list. - Each value must be convertible to its corresponding (by position) column type. - Each value literal can be an expression that evaluates to a simple value. ### `IF` clause - The `if_expression` can only apply to non-key columns (regular columns). - The `if_expression` can contain any logical and boolean operators. ### `USING` clause - `ttl_expression` must be an integer value (or a bind variable marker for prepared statements). - `timestamp_expression` must be an integer value (or a bind variable marker for prepared statements). ## Examples ### Insert a row into a table ```sql ycqlsh:example> CREATE TABLE employees(department_id INT, employee_id INT, name TEXT, PRIMARY KEY(department_id, employee_id)); ``` ```sql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John'); ``` ```sql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane'); ``` ```sql ycqlsh:example> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------ 1 | 1 | John 1 | 2 | Jane ``` ### Conditional insert using the `IF` clause Example 1 ```sql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe') IF name = null; ``` ```output [applied] ----------- True ``` Example 2 ```sql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Jack') IF NOT EXISTS; ``` ```output [applied] ----------- False ``` Example 3 ```sql ycqlsh:example> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------ 2 | 1 | Joe 1 | 1 | John 1 | 2 | Jane ``` ### Insert a row with expiration time using the `USING TTL` clause You can do this as follows: ```sql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 2, 'Jack') USING TTL 10; ``` Now query the employees table. ```sql ycqlsh:example> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------ 2 | 1 | Joe 2 | 2 | Jack 1 | 1 | John 1 | 2 | Jane ``` Again query the employees table after 11 seconds or more. ```sql ycqlsh:example> SELECT * FROM employees; -- 11 seconds after the insert. ``` ```output department_id | employee_id | name ---------------+-------------+------ 2 | 1 | Joe 1 | 1 | John 1 | 2 | Jane ``` ### Insert a row with `USING TIMESTAMP` clause #### Insert a row with a low timestamp ```sql ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jeff') USING TIMESTAMP 1000; ``` Now query the employees table. ```sql ycqlsh:foo> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------ 1 | 1 | John 1 | 2 | Jane 1 | 3 | Jeff 2 | 1 | Joe (4 rows) ``` #### Overwrite the row with a higher timestamp ```sql ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jerry') USING TIMESTAMP 2000; ``` ```sql ycqlsh:foo> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------- 1 | 1 | John 1 | 2 | Jane 1 | 3 | Jerry 2 | 1 | Joe (4 rows) ``` #### Try to overwrite the row with a lower timestamp ```sql ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'James') USING TIMESTAMP 1500; ``` ```sql ycqlsh:foo> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------- 1 | 1 | John 1 | 2 | Jane 1 | 3 | Jerry 2 | 1 | Joe (4 rows) ``` ### RETURNS STATUS AS ROW When executing a batch in YCQL, the protocol returns only one error or return status. The `RETURNS STATUS AS ROW` feature addresses this limitation and adds a status row for each statement. See examples in [batch docs](../batch#row-status). ## See also - [`CREATE TABLE`](../ddl_create_table) - [`DELETE`](../dml_delete/) - [`SELECT`](../dml_select/) - [`UPDATE`](../dml_update/) - [`Expression`](..#expressions)