INSERT INTO clause

Syntax

INSERT INTO name [ ( column [, ...]) ]

Usage

This form of the INSERT clause is used as the first clause of a Query statement whenever a query has a single destination. When branching into separate destinations, use the INSERT WHEN form of the INSERT clause. INSERT INTO must also appear as the first clause in an Insert Values statement. This clause indicates the destination for the statement. Rows are published to the specified destination any time the statement executes and produces output. The destination must be a named window or a local or output data stream.

INSERT INTO can also optionally specify one or more column names referring to the schema of the destination stream or window. This list explicitly indicates the columns to which data should be published, from left to right. The number and data types of the columns specified in the INSERT INTO clause must correspond to the number of items and data types of the select list in the statement's SELECT clause or column expressions in the VALUES clause. The left-most item in the select list or column expression list is published to the left-most column specified in the INSERT INTO clause; the next item is published to the next column indicated in the INSERTINTO, and so on, in order of select list or values list items and INSERTINTO columns.

Alternately, in the case of a Query statement you can explicitly match select list items with the destination's column names by using the AS output column reference syntax in the SELECT clause. See SELECT Clause for more information regarding this syntax.

When neither the INSERT INTO clause nor the SELECT clause (in the case of a Query statement) explicitly lists columns of the destination, the schema of the destination stream or window must entirely match the select list or column expression list in its number of columns and data types. Select or value list item output is then published to destination columns from left to right, as before, but based on the destination's entire schema, not the column specifications in the INSERTINTO.

If the destination stream or window includes more columns than you specify in the INSERT INTO, Sybase CEP Engine sets the unlisted columns to NULL when rows are published to the destination. If no columns are explicitly stated in the INSERT INTO, Sybase CEP Engine attempts to publish information to all columns of the destination.

Automatic Schema Creation

Under certain circumstances, you can use the INSERTINTO clause in a Query statement to create a name and schema for a previously undefined data stream. The data stream can then be used in subsequent CCL statements in the current module. Automatic schema creation can only be performed on a data stream that does not receive data from outside the current project.

The first Query statement that invokes the data stream must state the stream's name in the query's INSERT clause and list the stream's columns either in the INSERT clause, or in the SELECT clause. When the INSERT INTO form is used for this purpose, the clause must use the following syntax:

INSERT INTO stream ( column [, ...] )

Sybase CEP Engine then automatically creates the stream schema and data types for the stream. The data types for the columns of the automatic schema are determined by the data types of the data sources in the first Query statement that uses the stream as its destination.

Restrictions

• Input data streams cannot be designated as statement destinations. (An input stream is a type of Sybase CEP Engine stream that receives data from outside the current query module, either by way of bindings, or through a connection to an input adapter.)

• You cannot use duplicate column references in the INSERTINTO list of columns (you can, however, use duplicate column references in the SELECT clause of a Query statement).

• A Query statement that defines an automatic schema for a stream must appear in the query module before any CCL statement that uses the stream, either as a data source or destination.

See Also

• Insert Values Statement

• Query Statement

Example

The following example publishes the quantity and price of rows from InvoiceStream to OutStream, if the InvoiceStream rows have value in the Pretax column greater than 50. Total price is calculated by multiplying the Pretax column of InvoiceStream by 1.08 (simulating the amount of tax on the item). The quantity is published to the Quantity column of OutStream, and the calculated total price is published to OutStream's Price column.

INSERT INTO OutStream (Quantity, Price)
SELECT S.Quantity, S.Pretax * 1.08
FROM InvoiceStream AS S
WHERE S.Pretax > 50;