INSERT INTOtable_identifier
'('column_identifier[,...]')' SELECTtarget_list_1
FROMstream_expression
[RETURNINGtarget_list_2
] [ERROR INTOstream_identifier
];
table_identifier
-
The unique identifier (name) of the table into which to insert the new entries.
column_identifier
-
The unique identifier (name) of a column into which a new value will be inserted.
target_list
-
One or more entries, separated by commas, of the format target_list_entry. Entries in target_list_1 are inserted into the table; entries in target_list_2 are emitted as a tuple onto the output stream.
INSERT ... RETURNING
target_list_2
INTOstream_identifier
; target_list_entry
-
A value, of the format scalar_expression [AS output_field_identifier], to be included in the result set returned by the statement. .
scalar_expression
-
An expression that generates a value for a tuple field. Values can be obtained from a tuple field, from a simple function, or from the table. Optionally, the name for a value can be modified through an AS clause.
stream_expression
-
A stream expression, for example a SELECT clause, which provides the values to be inserted into the table.
output_field_identifier
-
A unique identifier (name) for the tuple field that contains a value returned by the insert operation
You can append an ERROR INTO clause just before the closing semicolon. The StreamSQL ERROR INTO clause is analogous to the Enable Error Output Port check box for operators and adapters in EventFlow applications.
Use ERROR INTO with the name of a stream, which must already exist. This sets up an Error Port for this operator, which is much like a local catch mechanism for errors from this operator.
See Using Error Ports and Error Streams for a discussion of StreamBase error handling mechanisms.
The INSERT statement inserts a new row into an existing table.
With the INSERT statement, the INTO clause lists the table fields that will be populated with data. The stream_expression
is a SELECT clause against a stream whose tuples contain the data that will be entered into the table. Its target list identifies
the tuple fields that will provide the content for each table field.
If all of the tuple fields will be entered into the table, and the fields are in an order that corresponds to the order of table fields, use * as the target list. Otherwise the target list must explicitly extract each tuple field, as illustrated below.
SELECT * FROMstream_identifier
SELECT tuple_field_identifier[, ...] FROMstream_identifier
In the RETURNING clause, scalar_expression
can specify a value from the stream and/or from the table. For example:
INSERT ... RETURNINGtuple_field_identifier
[AS ...][, ...],column_identifier
[AS ...][, ...]
The result set generated by the RETURNING clause must be captured into a stream. You can use the CREATE STREAM statement to define a stream and the INTO keyword to populate the stream with the content generated by the RETURNING clause. Alternatively, in a single statement you can use the => (arrow) operator with a CREATE STREAM statement, as illustrated below.
CREATE STREAMstream_identifier
; INSERT ... RETURNING ... INTOstream_identifier
;
Or
INSERT ... RETURNING ... => CREATE STREAM stream_identifier