Processes using SQL snippets often include result set variables that operate on temporary or staging tables. To avoid having to create these tables before a process starts and dropping them again when they are no longer needed, you can control the life cycle of a database table at the activity level by specifying preparation and cleanup statements.
To add life cycle settings to a result set reference in an SQL snippet:
Consider an SQL snippet with a SELECT statement that declares a result set reference with the preparation option set to “Always create new table” and the cleanup option set to “Drop table at end of process instance”. This means that the table is created just before it is filled with the result of the SELECT statement and dropped again when the process instance is stopped.
If the same set reference is again used as input in a subsequent UPDATE or INSERT statement, the default is used which is no preparation and cleanup.
Preparation and cleanup statements can be inferred from the context. For example, if the preparation option “Always create new table” is specified for a set reference used as the output of a query, an appropriate CREATE TABLE statement is inferred. The output table is created with the correct column type to fit the result of the query.
Preparation and cleanup statements are only inferred if no preparation and cleanup statements were defined for the result set reference as initial values.