--- title: "INSERT" url: "https://docs.yugabyte.com/stable/api/ycql/dml_insert/" --- # INSERT Use the INSERT statement to add a row to a specified table. ## Synopsis Use the `INSERT` statement to add a row to a specified table. ## Syntax ### Diagram INSERTINTO[table\_name](/stable/api/ycql/grammar_diagrams#table-name)(,[column\_name](/stable/api/ycql/grammar_diagrams#column-name))VALUES(,[expression](/stable/api/ycql/grammar_diagrams#expression))IFNOTEXISTS[if\_expression](/stable/api/ycql/grammar_diagrams#if-expression)USING[using\_expression](/stable/api/ycql/grammar_diagrams#using-expression)RETURNS STATUS AS ROW ### using\_expression ```ebnf using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression }; ``` AND[ttl\_or\_timestamp\_expression](/stable/api/ycql/grammar_diagrams#ttl-or-timestamp-expression) ### ttl\_or\_timestamp\_expression ```ebnf ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression; ``` TTL[ttl\_expression](/stable/api/ycql/grammar_diagrams#ttl-expression)TIMESTAMP[timestamp\_expression](/stable/api/ycql/grammar_diagrams#timestamp-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](/stable/api/ycql#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](/stable/api/ycql/ddl_create_table#table-properties-1 "transactions enabled"). - `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](/stable/api/ycql/batch#row-status "batch docs"). ## See also - [CREATE TABLE](/stable/api/ycql/ddl_create_table "CREATE TABLE") - [DELETE](/stable/api/ycql/dml_delete/ "DELETE") - [SELECT](/stable/api/ycql/dml_select/ "SELECT") - [UPDATE](/stable/api/ycql/dml_update/ "UPDATE") - [Expression](/stable/api/ycql#expressions "Expression")