INSERT

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide >

INSERT

Previous pageReturn to chapter overviewNext page

INSERT is used to insert rows into a table or stream. It can also be used in a pump to insert the output of one stream into another.

INSERT INTO table1 SELECT * FROM table2

INSERT INTO stream1 SELECT * FROM table1

CREATE PUMP COPY AS INSERT INTO stream1 SELECT * FROM stream2

Syntax

<insert statement> :=

  INSERT [ EXPEDITED ]

  INTO  <table-name > [ ( insert-column-specification ) ]

  < query  >

<insert-column-specification> := < simple-identifier-list >

<simple-identifier-list> :=

   <simple-identifier> [ , < simple-identifier-list > ]

For a discussion of VALUES, see SELECT.

Table Insert

If the identifier following INTO on the INSERT line refers to a table (see the <stream-or-table-reference> in the select-clause syntax chart ), and see <table-reference> in the FROM syntax, then the following statements will insert one or more rows into a table:

INSERT INTO emps (empno, name, deptno, gender)

VALUES (107, 'Jane Costa', 22, 'F');

INSERT INTO emps select * from new_emps;

Using INSERT to Upsert from Stream to Table

You can upsert from a stream to table, or vice versa, by using a statement along the following lines:

INSERT INTO "my_table" SELECT STREAM * from "my_stream";

 

s-Server native tables (those created within s-Server schemas using the CREATE TABLE statement) work well to copy stream data for the purpose of a display list table or other table for ephemeral data. This is especially useful for visualization tools which cannot handle streaming data. You can run regular upserts to the display list table, while allowing the visualization tool to ask for they want on their schedule, and let them to perform batch processing on their display data.

Stream Insert

If the identifier on the INSERT line refers to a stream, either of the following statements will insert a row into a stream:

INSERT INTO SALES.bids ("ticker", "shares", "price")

   VALUES ( 'ORCL', 300, 22.75);

1 row affected

INSERT INTO SALES.bids ("ticker", "shares", "price")

   SELECT "ticker", "shares", "price" FROM SALES."NewBidsTable";

 

The schema SALES is needed to qualify the "bids" stream or the "NewBidsTable"; and each column-name using lowercase letters must be enclosed in a pair of double-quotes.

Inserting into a stream results in a row being generated with no transaction involved.

Examples of stream insert generating rowtimes

Although the stream definition does not specify ROWTIME explicitly, every row has a rowtime. When the rowtime is not specified explicitly, the current value of the stream clock is used.

However, a rowtime can be specified for a stream insert by specifying the ROWTIME column (in the <insert-column-specification>). Rows inserted with rowtimes out of sequence are dropped. If no rowtime is specified, one is generated when it reaches the server.

INSERT INTO SALES.bids (ROWTIME, "ticker", "shares", "price")

. . . . . . . . . . . . . . .> VALUES (TIMESTAMP  '2008-06-21 12:01:01', 'ORCL', 300, 22.75);

1 row affected

 

Note: If a statement has just connected to a stream (by doing a JDBC prepare), then for the first row, it has to abide by the current rowtime of the stream. For subsequent rows, as long as it stays connected, the stream will wait for the slowest writer. (See Time and Streaming Data.)

Invalid example of a ROWTIME insertion

The code below is not valid for several reasons:

(A) Because, in the absence of explicit column targets, the number of values supplied must match the number of target columns in SALES.bids, which is six (as defined in the "LocalSQLstreamInstance" in SQLstream s-Studio). In this case, too few values are supplied.

(B) The rowtime value it attempts to insert has no explicit receiving parameter. In order to insert that timestamp as a rowtime, the ROWTIME column must be explicitly specified in the <insert-column-specification>, as it was in the example above.

0: jdbc:sqlstream:sdp://bento> INSERT INTO SALES.bids

. . . . . . . . . . . . . . .> VALUES (TIMESTAMP '2008-06-21 12:01:01', 'ORCL', 300, 22.75);

Error: From line 1, column 13 to line 1, column 16: Number of INSERT target columns (6) does not equal number of source items (4) (state=,code=0)

Rowtime considerations for insertions from multiple clients

The SQLstream system always ensures that the rows in a stream are in order, that is, that they have non-decreasing rowtimes.

In some instances, there can be two or more clients independently writing rows to the same stream S. Put another way, there can be two or more INSERT prepared statements running at the same time, with the same target stream S. (Of course, for efficiency, these should be INSERT EXPEDITED statements).

An INSERT statement can supply rows with an explicit ROWTIME by specifying <expression> AS ROWTIME in the list of expressions being inserted into S, or by including ROWTIME in the list of columns following 'INSERT INTO stream'.

In the absence of such a specification, rowtimes are implicit, which means the system assigns to each row the current system time at the moment it is received on the server.

When all INSERT statements have explicit rowtimes, SQLstream has to merge the inputs in rowtime order, so that S maintains correct order. This means the system can only accept a row from input #1 if it knows that the next row coming from input #2 will not have an earlier rowtime. So each input source has to wait until the other provides either its next row or a rowtime bound, which is a way of saying either "wait for me" or "go ahead."

When all INSERT statements have implicit rowtimes, there is no delay issue, because the system timestamps the rows as they come in.

A third case, where one inserter provides explicit rowtimes and one does not, would require delaying rows from the explicit side and allowing the other side's rows to go ahead immediately. This is not really practical to use because one inserter knows the time of day and the other inserter is oblivious.

For more detail on rowtime bounds, see the following:

The topic Sending Rowtime Bounds in this guide.

The topic SQLstream JDBC Driver in the Integration Guide.

The subtopics Rowtime Bounds: Forcing Timely Output and Extensions to JDBC API in the topic JDBC driver in the s-Server Integration Guide.

INSERT EXPEDITED

INSERT EXPEDITED is a SQLstream extension that uses SDP to communicate directly with a SQLstream s-Server. This allows the client to act like a native stream, and promotes higher performance client/server communication. (It is valid only with the JDBC driver, so for example, you can't have a PUMP based on INSERT EXPEDITED.)

Example:

INSERT EXPEDITED INTO SALES.bids (ROWTIME, "ticker", "shares", "price")

VALUES (?, ?, ?, ?);

Pump Stream Insert

INSERT may also be specified as part of a CREATE PUMP statement.

CREATE PUMP "HighBidsPump" AS INSERT INTO "highBids" ( "ticker", "shares", "price")

SELECT  "ticker", "shares", "price"

FROM SALES.bids

WHERE "shares"*"price">100000.00

 

Here the results to be inserted into the "highBids" stream should come from a <select> or UNION ALL expression that evaluates to a stream. This will create a continuously running stream insert. Rowtimes of the rows inserted will be inherited from the rowtimes of the rows output from the select or UNION ALL. Again rows may be initially dropped if other inserters, ahead of this inserter, have inserted rows with rowtimes later than those initially prepared by this inserter, since the latter would then be out of time order. See the topic CREATE PUMP in this guide.

Error Handling

If an runtime error (such as numeric overflow, null constraint violation etc.) occurs during INSERT EXPEDITED or during the operation of a PUMP, instead of aborting the operation and returning an error, the offending row is dropped and an error is reported to the error log. See the topic Error Handling in this guide.

Limitations

SQLstream cannot INSERT into a foreign table (see Updating JDBC Foreign Tables at http://farrago.sourceforge.net/design/medjdbc.html).

Syntax Diagram

insert01