Send more than one row

When you execute the INSERT statement, the statement sends one row of data to the database server. When an INSERT statement sends more than one row, define an insert cursor with the DECLARE statement. An insert cursor enables you to buffer multiple rows of data for insertion at one time. The DECLARE statement associates the INSERT statement with the insert cursor. In the DECLARE statement, the INSERT statement can be in either of the following formats:

• A literal INSERT statement in the DECLARE statement
  The following DECLARE statement associates a literal INSERT statement with the ins1_curs cursor:

  EXEC SQL declare ins1_curs cursor for 
        insert into customer values;

• A prepared INSERT statement in the DECLARE statement
  The following DECLARE statement associates a prepared INSERT statement with the ins2_curs cursor:

  EXEC SQL prepare ins_stmt from
        'insert into customer values';
  EXEC SQL declare ins2_curs cursor for ins_stmt;

If you use an insert cursor it can be much more efficient than if you insert rows one at a time, because the application process does not need to send new rows to the database as often. You can use a sequential or hold cursor for the insert cursor. The following table summarizes the SQL statements that manage an insert cursor.

Table 1. SQL statements that manage an insert cursor

Task	Insert cursor
Declare the cursor ID	DECLARE associated with an INSERT statement
Execute the statement	OPEN
Send a single row from the program into the insert buffer	PUT
Clear the insert buffer and send the contents to the database server	FLUSH
Close the cursor	CLOSE
Free cursor resources	FREE