skip to main content
Supported SQL Functionality : Insert
  

Try DataDirect Drivers Now

Insert

Purpose

The Insert statement is used to add new rows to a local table. You can specify either of the following options:
*List of values to be inserted as a new row
*Select statement that copies data from another table to be inserted as a set of new rows
In Cassandra, Inserts are in effect Upserts. When an Insert is performed on a row that already exists, the row will be updated.

Syntax

INSERT INTO table_name [(column_name[,column_name]...)] {VALUES (expression [,expression]...) | select_statement}
table_name
is the name of the table in which you want to insert rows.
column_name
is optional and specifies an existing column. Multiple column names (a column list) must be separated by commas. A column list provides the name and order of the columns, the values of which are specified in the Values clause. If you omit a column_name or a column list, the value expressions must provide values for all columns defined in the table and must be in the same order that the columns are defined for the table. Table columns that do not appear in the column list are populated with the default value, or with NULL if no default value is specified.
expression
is the list of expressions that provides the values for the columns of the new record. Typically, the expressions are constant values for the columns. Character string values must be enclosed in single quotation marks (’). See Literals for more information.
select_statement
is a query that returns values for each column_name value specified in the column list. Using a Select statement instead of a list of value expressions lets you select a set of rows from one table and insert it into another table using a single Insert statement. The Select statement is evaluated before any values are inserted. This query cannot be made on the table into which values are inserted. See Select for information about Select statements.

Notes

*Insert is supported for primitive types and non-nested complex types. See "Complex Type Normalization" for details.
*The driver supports an insert on a child table prior to an insert on a parent table, circumventing referential integrity constraints associated with traditional RDBMS. To maintain integrity between parent and child tables, it is recommended that an insert be performed on the parent table for each foreign key value added to the child. If such an insert is not first performed, the driver automatically inserts a row into the parent tables that contains only the primary key values and NULL values for all non-primary key columns. See "Complex Type Normalization" for details.
*To enable Insert, Update, and Delete, set the ReadOnly connection property to false.

See also

Complex Type Normalization