sa_copy_cursor_to_temp_table( 
cursor_name 
, table_name 
[, first_row
[, max_rows ] ]
)

Suppose you have a cursor of several integer columns. sa_copy_cursor_to_temp_table creates a temporary table using a statement in this form:

BEGIN
  CREATE LOCAL TEMPORARY TABLE TempTab (
      col1 INT,
      col2 INT,
  ...
rownum bigint primary key )
END;

sa_copy_cursor_to_temp_table names the columns col1,col2, and so on to avoid duplication of names or difficulty if cursor columns do not have a well defined name (for example, if they are a complex expression).

Once the temporary table is created, the contents of the open cursor are inserted by moving to the row number indicated by first_row, and inserting the number of rows indicated by max_rows. After the contents have been inserted into the temporary table, the cursor is re-positioned at its original location.

None

Copying from the cursor fetches the rows using the cursor's isolation settings. This may acquire locks on rows and have other effects equivalent to fetching from the cursor.

If concurrent changes are made outside of the current connection and the cursor is not protected from these by materialization or isolation settings, then it is possible that the cursor will be positioned on a different row after the procedure completes. For example, if the previous current row of the cursor was deleted, the cursor could be repositioned on the row after the original position.

If an error occurs while copying from the cursor, the cursor enters an invalid state, and further operations on the cursor fail with an error.

• sa_list_cursors system procedure
• sa_describe_cursor system procedure

The following batch creates a cursor named myCursor and loads it with data from the Products table. The cursor is then opened (OPEN statement). A DROP statement drops myTempTable, if it already exists. Calling sa_copy_cursor_to_temp_table creates a temporary table called myTempTable and copies the contents of myCursor into it. Finally, a SELECT statement returns the data that was copied into the temporary table from the cursor:

BEGIN 
  DECLARE myCursor CURSOR FOR 
      SELECT ID, Name, Description, Color, Quantity FROM Products;
  OPEN myCursor;
  DROP TABLE IF EXISTS myTempTable;
  CALL sa_copy_cursor_to_temp_table( 'myCursor', 'myTempTable' );
  CLOSE myCursor;
  SELECT * FROM myTempTable;
END