INSERT

An INSERT statement creates rows or columns and stores them in the named table. The number of values assigned in an INSERT statement must be the same as the number of specified or implied columns.

Whenever you insert into a table which has generated columns, Splice Machine calculates the values of those columns.

Syntax

table-Name

The table into which you are inserting data.

Simple-column-Name*

An optional list of names of the columns to populate with data.

Query [ORDER BY clause]

A SELECT or VALUES command that provides the columns and rows of data to insert. The query can also be a UNION expression.

See the Using the ORDER BY Clause section below for information about using the ORDER BY clause.

Single-row and multiple-row VALUES expressions can include the keyword DEFAULT. Specifying DEFAULT for a column inserts the column’s default value into the column. Another way to insert the default value into the column is to omit the column from the column list and only insert values into other columns in the table. For more information, see VALUES expression

result offset and fetch first clauses

The  fetch first clause, which can be combined with the result offset clause, limits the number of rows added to the table.

Using the ORDER BY Clause

When you want insertion to happen with a specific ordering (for example, in conjunction with auto-generated keys), it can be useful to specify an ORDER BY clause on the result set to be inserted.

If the Query is a VALUES expression, it cannot contain or be followed by an ORDER BY, result offset, or fetch first clause. However, if the VALUES expression does not contain the DEFAULT keyword, the VALUES clause can be put in a subquery and ordered, as in the following statement:

INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;

For more information about queries, see Query.

Examples

These examples insert records with literal values:

splice> INSERT INTO Players
   VALUES( 99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991');
1 row inserted/updated/deleted

splice> INSERT INTO Players
   VALUES (99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991'),
          (73, 'Giants', 'Lester Johns', 'P', 'Big John', '06/09/1984'),
          (27, 'Cards', 'Earl Hastings', 'OF', 'Speedy Earl', '04/22/1982');
3 rows inserted/updated/deleted

This example creates a table name OldGuys that has the same columns as our Players table, and then loads that table with the data from Players for all players born before 1980:

splice> CREATE TABLE OldGuys(
    ID           SMALLINT NOT NULL PRIMARY KEY,
    Team         VARCHAR(64) NOT NULL,
    Name         VARCHAR(64) NOT NULL,
    Position     CHAR(2),
    DisplayName  VARCHAR(24),
    BirthDate    DATE
    );

splice> INSERT INTO OldGuys
   SELECT * FROM Players
   WHERE BirthDate < '01/01/1980';

Statement dependency system

The INSERT statement depends on the table being inserted into, all of the conglomerates (units of storage such as heaps or indexes) for that table, and any other table named in the statement. Any statement that creates or drops an index or a constraint for the target table of a prepared INSERT statement invalidates the prepared INSERT statement.

See Also