INSERT

Documentation

Home » Documentation » Using VoltDB

INSERT

INSERT — Creates a new row in the database, using the specified values for the columns.

Synopsis

INSERT INTO table-name [( column-name [,...] )] VALUES ( value-expression [,...] )

INSERT INTO table-name [( column-name [,...] )] SELECT select-expression

Description

The INSERT statement creates a new row in the database. There are two forms the the INSERT statement, INSERT INTO... VALUES and INSERT INTO... SELECT. The INSERT INTO... VALUES statement lets you enter specific values for a adding a single row to the database. The INSERT INTO... SELECT statement lets you insert multiple rows into the database, depending upon the number of rows returned by the select expression.

The INSERT INTO... SELECT statement is often used for copying rows from one table to another. For example, say you want to export all of the records associated with a particular column value. The following INSERT statement copies all of the records from the table ORDERS with a warehouseID of 25 into the table EXPORT_ORDERS:

INSERT INTO Export_Orders SELECT * FROM Orders WHERE CustomerID=25;

However, the select expression can be more complex, including joining multiple tables. The following limitations currently apply to the INSERT INTO... SELECT statement:

  • INSERT INTO... SELECT can only be performed as part of a single-partitioned procedure.

  • INSERT INTO... SELECT cannot be executed as an ad hoc statement (either through @AdHoc or using sqlcmd).

  • INSERT INTO... SELECT does not support UNION statements.

Deterministic behavior is critical to maintaining the integrity of the data in a K-safe cluster. Because an INSERT INTO... SELECT statement performs both a query and an insert based on the results of that query, if the selection expression would produces non-deterministic results, the VoltDB query planner rejects the statement and returns an error. See Section 3.2.2, “VoltDB Stored Procedures and Determinism” for more information on the importance of determinism in SQL queries.

If you specify the column names following the table name, the values will be assigned to the columns in the order specified. If you do not specify the column names, values will be assigned to columns based on the order specified in the schema definition. However, if you specify a subset of the columns, you must specify values for any columns that are explicitly defined in the schema as NOT NULL and do not have a default value assigned.

VoltDB supports the following arithmetic operators for expressions in the VALUES clause: addition (+), subtraction (-), multiplication (*), and division (*).

Examples

The following example inserts values into the columns (firstname, mi, lastname, and emp_id) of an EMPLOYEE table:

INSERT INTO employee VALUES ('Jane', 'Q', 'Public', 145303);

The next example performs the same operation with the same results, except this INSERT statement explicitly identifies the column names and changes the order:

INSERT INTO employee (emp_id, lastname, firstname, mi) 
       VALUES (145303, 'Public', 'Jane', 'Q');

The last example assigns values for the employee ID and the first and last names, but not the middle initial. This query will only succeed if the MI column is nullable or has a default value defined in the database schema.

INSERT INTO employee (emp_id, lastname, firstname) 
       VALUES (145304, "Doe", "John");