INSERT
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
The INSERT statement inserts rows into a table.
This statement has the following format:
[REPEATED]]INSERT INTO [schema.]table_name
[(column {, column})]
[OVERRIDING SYSTEM VALUE | OVERRIDING USER VALUE]
[VALUES (expr{, expr}) {,(expr{ ,expr})} |
[WITH common_table_expression] subselect];
REPEATED
Saves the execution plan of the insert, which can make subsequent executions faster.
column {,column}
Identifies the columns of the specified table into which the values are placed. When the column list is included, the DBMS Server places the result of the first expr in the values list or subselect into the first column named, the second value into the second column named, and so on. The data types of the values must be compatible with the data types of the columns in which they are placed.
OVERRIDING SYSTEM VALUE
Overrides the sequence value for a GENERATED ALWAYS AS IDENTITY column with the explicit value specified in the VALUES clause. Cannot be used on an INSERT...SELECT statement.
OVERRIDING USER VALUE
Overrides the sequence value for a GENERATED BY DEFAULT IDENTITY column with the explicit value specified in the VALUES clause. Cannot be used on an INSERT...SELECT statement.
VALUES (expr{ ,expr}) {,(expr{ ,expr})} | subselect
Specifies the values to be inserted as one of the following:
• One or more comma-separated sets of lists of expressions, each representing one row of values for insertion. The expressions in each row must correspond with the column list specified.
INSERT INTO tbl (col1,col2) VALUES (1,’a’), (2,’b’), (3,‘c’);
• A subselect, which inserts all the rows that result from the evaluation of the subselect.
INSERT INTO tbl (col1,col2) SELECT a, b FROM tbl2;
If a column corresponding to an expr is the identity column and there is no OVERRIDING clause, the value must be DEFAULT.
WITH common_table_expression