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.



The table into which you are inserting data.


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:


For more information about queries, see Query.


If the target table (the table into which you’re inserting) has a Primary Key, you can use the INSERTMODE hint to specify that you want the insert operation to be an UPSERT, which means that:

  • If the source row contains a primary key value that already exists in the target table, then update the existing row in the target table with values from the source row.
  • If the source row contains a primary key value that does not exist in the target table, then insert the source row.

You specify the INSERTMODE hint following the table and optional column names; for example:

INSERT INTO t1(a1, a2) --splice-properties insertMode=UPSERT
SELECT a2, b2 from t2;

The INSERTMODE hint, like other Splice Machine hints, must be used after the table identifier, and must be at the end of a line, followed by a newline character.

Currently, the INSERTMODE hint can only have two values:

UPSERT Specifies that an upsert operation is to be used.
INSERT Specifies that an insert operation is to be used. This is the default value, and thus does not require hinting.

Upsert Restrictions

Upsert can only be used when the target table meets these restrictions:

  • The target table must have a primary key; if you specify the UPSERT hint and the table does not have a primary key, the operation will fail.
  • The target table also cannot contain any auto-generated columns; if it does, the auto-generated column values will not be updated correctly.


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(
    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';

Upsert Example

This example demonstrates using the INSERTMODE hint to update matching rows:

INSERT INTO t1 VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6);

CREATE TABLE t2 (a2 INT, b2 INT, c2 INT);
INSERT INTO t2 VALUES (1,10,10), (2,20,20), (10,10,10);
splice> SELECT * FROM t1;
A1         |B1         |C1
1          |1          |1
2          |2          |2
3          |3          |3
4          |4          |4
5          |5          |5
6          |6          |6

6 rows selected

INSERT INTO t1(a1, b1) --splice-properties insertMode=UPSERT
SELECT a2, b2 FROM t2;

3 rows inserted/updated/deleted

A1         |B1         |C1
1          |10         |1  <== updated row based on the PK value A1
2          |20         |2  <== updated row based on the PK value A1
3          |3          |3
4          |4          |4
5          |5          |5
6          |6          |6
10         |10         |NULL   <== inserted row

7 rows selected

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