INSERT

INSERT is used to insert rows into a table or stream. You specify columns in the table or stream by simple identifier (such as columns called name or “ticker”), and then specify a query (a VALUES or SELECT statement) from which rows will be inserted. Specified columns for the target stream or table must match columns in the VALUES or SELECT statement.

You can use an INSERT statement inside a pump to insert the output of one stream into a stream or table. Pumps can be started and stopped, and can be included in schemas so that you can start or stop an entire pipeline at once.

Syntax

<options> are implemented for versions after s-Server 6.0.


<insert statement> :=
  INSERT [ EXPEDITED ]
  INTO  <table-name > [(<simple-identifier> [,<simple-identifier> ]* )].
   <options>
  < query  >  
<options> ::= WITH OPTIONS( <option> [, <option>]* )
<option> ::= <optionName> <optionValue>
<optionName> ::= BULK_LOAD | TRANSACTION_ROWTIME_LIMIT | TRANSACTION_ROW_LIMIT | PRESERVE_PARTIAL_WRITES | RETRY_COUNT | RETRY_MAX_TIME | RETRY_PAUSE_MIN | RETRY_PAUSE_MAX
<optionValue> ::= string constant

Table Insert

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;

For a discussion of VALUES, see VALUES.

Stream Insert

Either of the following statements will insert a row into a stream:

INSERT INTO SALES.bids ("ticker", "shares", "price")
   VALUES ( 'ORCL', 300, 22.75);

INSERT INTO SALES.bids ("ticker", "shares", "price")
   SELECT "ticker", "shares", "price" FROM SALES."NewBidsTable";

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

Examples of Stream Insert Generating Rowtimes

Every row in a stream has a rowtime. For INSERTS from a foreign stream into a native stream, you can set rowtimes explicitly by promoting a column from your source to rowtime. For INSERTS from a foreign stream into a native stream, when the rowtime is not specified explicitly, rows are assigned rowtimes implicitly: their rowtime is the timestamp when the row entered s-Server.

For INSERTS from native stream to another native stream, rowtimes are passed through unchanged unless you specifically promote a column to ROWTIME.

If you specify ROWTIME in the INSERT statement, then inserted with rowtimes out of sequence are dropped.

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

. . . . . . . . . . . . . . .> VALUES (TIMESTAMP  '2020-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.

INSERT INTO SALES.bids

VALUES (TIMESTAMP '2020-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)

First, 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.

Second, 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

DML Options

Guavus SQLstream 6.0 introduces DML options for INSERT and MERGE. These options override stream/table and server options. Currently available options are as follows:

Option Option Type Definition
BULK_LOAD DML option on INSERT/MERGE only You can use this option for insert/merges with tables as sources only–a type of statement known as “finite DML”. This is a Boolean operator. When this option is set to “true”, s-Server will commit rows whenever a batch of buffered rows is flushed to the external database. The statement will fail if the target database raises an exception. In that case, the failing batch will be rolled back but previous batches will remain committed. Note: infinite DML always commits on batch boundaries.
PRESERVE_PARTIAL_WRITES DML option on INSERT/MERGE only True or false. Defaults to false. If true, when a row fails to write to an RDBMS table, the failed row is discarded, but the statement continues to run.This will allow other rows to be written to the RDBMS table, depending on how the database’s JDBC driver handles java.sql.Statement.executeBatch().
TRANSACTION_ROWTIME_LIMIT DML option on INSERT/MERGE, table, server options You can use this option for insert/merges with streams as sources only–a type of statement known as “infinite DML”. This is the number of milliseconds to collect rows before committing.
TRANSACTION_ROW_LIMIT DML option on INSERT/MERGE, table, server options This is the number of rows to collect before committing.
RETRY_COUNT DML option on INSERT/MERGE only The number of times that s-Server tries rewriting a batch of rows that fails because of a transient error. -1 means “no maximum number of retries”. Other negative values (and non-integer values) are illegal. For finite DML, RETRY_COUNT defaults to 0. For infinite DML, it defaults to -1.Each retry attempt will pause longer than the previous attempt.
RETRY_MAX_TIME DML option on INSERT/MERGE only The maximum number of milliseconds that s-Server will spend retrying a failed batch. It defaults to -1, which means “no maximum time”. RETRY_MAX_TIME = 0 means the same thing as RETRY_COUNT = 0: don’t retry at all.
RETRY_PAUSE_MIN DML option on INSERT/MERGE only The minimum number of milliseconds to pause between retries. It defaults to 10,000 (10 seconds). The value must be a positive integer–anything else raises an error.
RETRY_PAUSE_MAX DML option on INSERT/MERGE only The maximum number of milliseconds to pause between retries. It defaults to 300,000 (5 minutes). The value must be a positive integer–anything else raises an error.

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 monotonically increasing 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 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:

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 a 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.

Errors work slightly differently when you insert from a stream into a foreign table (in an RDBMS system). Because you are inserting from a stream, this insert will run forever until you terminate it. That is, errors do not terminate infinite DML, which is what we call DML with a stream involved. When s-Server encounters an error in these circumstances, it logs it to the error stream and continues to run the INSERT statement.

But because you’re working with an RDBMS system, that can sometimes mean that rows are not being inserted even though the INSERT statement continues to run, because the table in the RDBMS system may be configured to terminate INSERTs when the system encounters an error. You may want to configure your RDMBS system so that it does not terminate DML statements when errors are encountered.

First, here’s how INSERT statements work for stream > foreign table INSERTS: As the INSERT statement runs, s-Server buffers up rows and flushes them to the foreign table. The boundaries of these batches are defined by the table’s commit policy. If an error occurs while flushing a row (for example, a row violates a constraint), then the offending row will not commit.

It is up to the external database whether to commit or discard later rows in that batch. That commit/discard behavior is specified by the behavior of java.sql.Statement.executeBatch(), as described in the documentation for the database’s JDBC driver.

Syntax Diagram