Insert Cursor

About this task

When you associate an INSERT statement with a cursor, the cursor is called an Insert cursor. An Insert cursor is a data structure that represents the rows that the INSERT statement is to add to the database. The Insert cursor simply inserts rows of data; it cannot be used to fetch data. To create an Insert cursor, you associate a cursor with a restricted form of the INSERT statement. The INSERT statement must include a VALUES clause; it cannot contain an embedded SELECT statement.

Create an Insert cursor if you want to add multiple rows to the database in an INSERT operation. An Insert cursor allows bulk insert data to be buffered in memory and written to disk when the buffer is full, as these steps describe:
  1. Use DECLARE to define an Insert cursor for the INSERT statement.
  2. Open the cursor with the OPEN statement. The database server creates the insert buffer in memory and positions the cursor at the first row of the insert buffer.
  3. Copy successive rows of data into the insert buffer with the PUT statement.
  4. The database server writes the rows to disk only when the buffer is full. You can use the CLOSE, FLUSH, or COMMIT WORK statement to flush the buffer when it is less than full. This topic is discussed further under the PUT and CLOSE statements.
  5. Close the cursor with the CLOSE statement when the insert cursor is no longer needed. You must close an Insert cursor to insert any buffered rows into the database before the program ends. You can lose data if you do not close the cursor properly.
  6. Free the cursor with the FREE statement. The FREE statement releases the resources that are allocated for an Insert cursor.

Using an Insert cursor is more efficient than embedding the INSERT statement directly. This process reduces communication between the program and the database server and also increases the speed of the insertions.

Insert cursors also have the sequential cursor characteristic. To create an Insert cursor, you associate a sequential cursor with a restricted form of the INSERT statement. (For more information, see Insert Cursor.) The following example contains code that declares a sequential Insert cursor: 
EXEC SQL declare ins_cur cursor for
   insert into stock values
   (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);