Page History
...
Fields | Description | ||
---|---|---|---|
Template | Enables you to generate update procedures via a template. | ||
Business Key Columns | Columns that define the business key for update processing. This is required for include Update options.
For example, a source table may have a unique constraint on both a product code and a product description. Therefore, the description as well as the code must be unique.
| ||
Parameters | Any parameters selected are included in the generated update procedure as variables. The procedure will include code to retrieve the value of the parameter at run time and store it in the declared variable. Clicking on the ellipsis button opens the Parameters selection screen. The variables can also be used in column transformations and in the from/where clause for the update procedure. Some databases have a 30-character limit for variable names. WhereScape RED ensures the variables added for any parameters are less than 30 characters long by creating variable names in the form v_ followed by the first 28 characters of the parameter name.
| ||
Include Initial Load Insert | Adds an additional insert statement to the update procedure that runs if the target Data Store is empty. The benefit of this is improved performance inserting into an empty table without performing any checks to see if rows already exist. The default for this field is off (i.e. an initial insert statement is not added to the procedure). | ||
Process by Batch | Allows users to select a column to drive data processing in a loop based on the distinct ordered values of the selected columns. The update procedure loops on this column and performs the delete, update and/or insert for each value. If the column chosen is a date datatype (date, datetime or timestamp), then the user can specify yearly, monthly, daily or column level looping. The default for this field is off (do not do batch processing). | ||
Delete Before Insert | Allows selection of how to process deletes. It enables a delete statement to be added to the update procedure before any update or insert statement. This is a particularly useful option for purging old data and for updates based on a source system batch number. | ||
Issue Warning if a Delete occurs | This option sets the procedure to a warning state if any deletes occur. | ||
Delete Where Clause | The delete where clause is appended to the generated delete statement to constrain the rows deleted. | ||
Process Method | Select between Insert/Update and Merge which enables you to use the Merge statement instead of two separate Insert and update statements. | ||
Include Insert Statement | Includes the insert statement in the procedure to insert new rows in the Data Store. | ||
Insert Hint | Enter a database hint to be used in the INSERT statement. This is a SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options. | ||
Insert New Rows Only | Uses change detection to work out what rows require inserting. | ||
New Row Identification Method | Method used to identify that records in source are not currently recorded in the target table. | ||
Existing Data Selection Hint | Database-compliant hint to be used for the existing data select statement. | ||
Include Update Statement | Includes an update statement in the procedure to update changing rows in the Data Store. If this option is chosen, then the Update Changed rows only option is available. | ||
Include Hint | Enter a database hint to be used in the INCLUDE statement. This is a SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options. | ||
Update Changed Rows Only | Uses change detection to work out what rows require updating. Choosing this option, enables the Change Row identification Method. | ||
Change Row Identification Method | Method used to identify that records in source have changed from what is currently recorded in the target table. | ||
Existing Data Selection Hint | Database-compliant hint to be used for the existing data select statement. | ||
Merge Hint | Enter a database hint to be used in the MERGE statement. This is a SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options. |
...