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.
INSERT INTO table-Name [ (Simple-column-Name]* ) ] Query [ ORDER BY clause ] [ result offset clause ] [ fetch first clause ];
The table into which you are inserting data.
An optional list of names of the columns to populate with data.
Query [ORDER BY clause]
VALUES command that provides the columns and rows of
data to insert. The query can also be a
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 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
result offset and fetch first clauses
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
ORDER BY, result offset, or fetch first clause. However, if the
VALUES expression does not contain the
DEFAULT keyword, the
clause can be put in a subquery and ordered, as in the following
INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;
For more information about queries, see Query.
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
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
INSERT statement invalidates the prepared