.. highlight:: cql

.. _insert-statement:

INSERT
^^^^^^

Inserting data for a row is done using an ``INSERT`` statement:

.. code-block::
   
   insert_statement:  INSERT INTO table_name ( `names_values` | `json_clause` )
                   :  [ IF NOT EXISTS ]
                   :  [ USING `update_parameter` ( AND `update_parameter` )* ];
   names_values: `names` VALUES `tuple_literal`
   json_clause:  JSON `string` [ DEFAULT ( NULL | UNSET ) ]
   names: '(' `column_name` ( ',' `column_name` )* ')'
   update_parameter: ( TIMESTAMP `int_value` | TTL  `int_value` | TIMEOUT `duration` )
   int_value: ( `integer` | `bind_marker` )

For example:

.. code-block:: cql

    INSERT INTO NerdMovies (movie, director, main_actor, year)
          VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005)
          USING TTL 86400 IF NOT EXISTS;

The ``INSERT`` statement writes one or more columns for a given row in a table. Note that since a row is identified by
its ``PRIMARY KEY``, at least the columns composing it must be specified. The list of columns to insert to must be
supplied when using the ``VALUES`` syntax.

Note that unlike in SQL, ``INSERT`` does not check the prior existence of the row by default: the row is created if none
existed before, and updated otherwise. Furthermore, there is no means to know which of creation or update happened.

All updates of an ``INSERT`` are applied atomically, meaning the
statement can not have a partial effect on database state.

It can, however, leave some of the columns unchanged due to the semantics
of eventual consistency on an event of a timestamp collision:

``INSERT`` statements happening concurrently at different cluster
nodes proceed without coordination. Eventually cell values
supplied by a statement with the highest timestamp will prevail (see :ref:`update ordering <update-ordering>`).

Unless a timestamp is provided by the client, ScyllaDB will automatically
generate a timestamp with microsecond precision for each
column assigned by ``INSERT``. ScyllaDB ensures timestamps created
by the same node are unique. Timestamps assigned at different
nodes are not guaranteed to be globally unique.
With a steadily high write rate timestamp collision
is not unlikely. If it happens, i.e. two ``INSERTS`` have the same
timestamp, a conflict resolution algorithm determines which of the inserted cells prevails (see :ref:`update ordering <update-ordering>`).

Please refer to the :ref:`update parameters <update-parameters>` section for more information on the :token:`update_parameter`.

.. code-block:: cql

    INSERT INTO NerdMovies  (movie, director, main_actor)
    VALUES ('Serenity', 'Anonymous', 'Unknown')
    USING TIMESTAMP  1442880000000000;
    INSERT INTO NerdMovies  (movie, director, main_actor)
    VALUES ('Serenity', 'Joseph Whedon', 'Nathan Fillion')
    USING TIMESTAMP 1442880000000000;

    SELECT movie, director, main_actor FROM NerdMovies WHERE movie = 'Serenity'

.. code-block:: none

   movie    | director      | main_actor
   ----------+---------------+------------
   Serenity | Joseph Whedon |    Unknown


``INSERT`` is not required to assign all columns, so if two
statements modify the same primary key but assign different
columns effects of both statements are preserved:

.. code-block:: cql

    INSERT INTO NerdMovies (movie, director, main_actor)
          VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion');
    INSERT INTO NerdMovies (movie, director, year)
          VALUES ('Serenity', 'Josseph Hill Whedon', 2005);
    SELECT * FROM NerdMovies WHERE movie = 'Serenity'

.. code-block:: none

   ╭─────────┬───────────────────┬──────────────┬─────╮
   │movie    │director           │main_actor    │year │
   ├─────────┼───────────────────┼──────────────┼─────┤
   │Serenity │Joseph Hill Whedon │Nathan Fillion│2005 │
   ╰─────────┴───────────────────┴──────────────┴─────╯

Also note that ``INSERT`` does not support counters, while ``UPDATE`` does.

.. note:: You can use the ``IF NOT EXISTS`` condition with the ``INSERT`` statement. When this is used, the insert is only made if the row does not exist prior to the insertion. Each such ``INSERT`` gets a globally unique timestamp. Using ``IF NOT EXISTS`` incurs a non-negligible performance cost (internally, as Paxos will be used), so use ``IF NOT EXISTS`` wisely.


If :ref:`enabled <cdc-options>` on a table, you can use UPDATE, INSERT, and DELETE statements with Change Data Capture (CDC) tables.

.. to-do - add link to cdc doc


:doc:`Apache Cassandra Query Language (CQL) Reference </cql/index>`

.. include:: /rst_include/apache-copyrights.rst


.. Licensed to the Apache Software Foundation (ASF) under one
.. or more contributor license agreements.  See the NOTICE file
.. distributed with this work for additional information
.. regarding copyright ownership.  The ASF licenses this file
.. to you under the Apache License, Version 2.0 (the
.. "License"); you may not use this file except in compliance
.. with the License.  You may obtain a copy of the License at
..
..     http://www.apache.org/licenses/LICENSE-2.0
..
.. Unless required by applicable law or agreed to in writing, software
.. distributed under the License is distributed on an "AS IS" BASIS,
.. WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
.. See the License for the specific language governing permissions and
.. limitations under the License.