While the default FILL behavior maintains relational integrity, there are cases in which you want to control the population of data records. You can set the FILL-MODE attribute for a temp-table buffer to specify how records are added to the dataset.

How FILL-MODE works

The FILL( ) method can be executed multiple times to add more data to a dataset temp-table. To control how the records are added, you choose a mode for the FILL method.

By default, each dataset temp-table is in "MERGE" mode. When you populate the temp-table using the FILL method in "MERGE" mode, duplicate records (determined by a unique temp-table index) are not added during the FILL operation. A unique primary temp-table index must exist so that FILL can identify duplicate records.

Note: Ensure FILL-MODE is set prior to executing a FILL operation.

The following are the modes that you can specify for each temp-table buffer in the dataset before you execute a FILL.

FILL-MODE Description
MERGE (Default) Adds records. Duplicate records (determined by a unique temp-table index) are not added during a FILL and no error condition is raised if duplicates are found.
EMPTY Empties the temp-table before a FILL begins; can be used to“refresh” a temp-table or dataset by emptying the temp-tables before populating them.
APPEND Raises an error condition when duplicates are found and the FILL process is aborted. Use "APPEND" when you know there are no duplicates (for example, if you are adding batches of records).
REPLACE If a row with the same unique primary key values is already present, its field values are replaced by the field values from the data-source. If the row is not already there, it is created in the temp-table.
NO-FILL The temp-table is not filled. This mode is useful if you are filling a dataset that already has data in it and you want to suppress the FILL for a particular temp-table.

Set the FILL-MODE

The following is the syntax for setting FILL-MODE on a temp-table buffer:
buffer-handle:FILL-MODE = [ "MERGE" | "EMPTY" | "APPEND" | "REPLACE" | "NO-FILL" ].

The following code example shows how to set the FILL-MODE:

/* set FILL-MODE for each of the temp-tables */
BUFFER ttOrder:FILL-MODE = "APPEND".
BUFFER ttOrderLine:FILL-MODE = "APPEND".

Empty the table before the FILL

If you already loaded a table with data using FILL, and want to empty the table before adding more records to it, you set the FILL-MODE to "EMPTY":

BUFFER ttOrder:FILL-MODE = "EMPTY".

In this example, the next FILL operation empties a temp-table called ttOrder before populating it again.

See also

Control the filling of each table