Archive
Archive
INSERT — Creates new rows in the database, using the specified values for the columns.
INSERT INTO table-name [( column-name [,...] )] VALUES ( value-expression [,...] )
INSERT INTO table-name [( column-name [,...] )] SELECT select-expression
The INSERT statement creates one or more new rows in the database. There are two forms of 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 join partitioned tables only if they are joined on equality of the partitioning columns. Also, the resulting INSERT must apply to a partitioned table and be inserted using the same partition column value, whether the query is executed in a single-partitioned or multi-partitioned stored procedure.
INSERT INTO... SELECT does not support UNION statements.
In addition to the preceding limitations, there are certain instances where the select expression is too complex to be processed. Cases of invalid select expressions in INSERT INTO... SELECT include:
A LIMIT or TOP clause applied to a partitioned table in a multi-partitioned query
A GROUP BY of a partitioned table where the partitioning column is not in the GROUP BY clause
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 5.1.2, “VoltDB Stored Procedures are Deterministic” 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.
You can use subqueries within the VALUES clause of the INSERT statement, with the following provisions:
See the description of subqueries in the SELECT statement for general rules concerning the construction of subqueries.
In a multi-partition procedure, subqueries of the INSERT statement can only reference replicated tables.
In single-partitioned procedures, the subquery can reference both partitioned and replicated tables.
For ad hoc INSERT statements, the same rules apply except the SQL statement itself determines whether VoltDB executes it as a single-partitoned or multi-partitioned procedure. Statements that insert rows into a partitioned table based on a specific value of the partitioning column are executed as single-partitioned procedures. All other statements are multi-partitioned.
The following example inserts values into the columns (firstname, mi, lastname, and emp_id) of an EMPLOYEE table:
V11.x DocumentationINSERT 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');