When you first create a task, you must select a Task Method. The Task Method is a collection of default EDT settings that match common task requirements as shown below:
Let's take a closer look at each of these five Task Methods and their typical uses:
|Report / stored query
This would typically be a reporting task. Select this option if the purpose of the task is to produce a tabular report. No data in either the source or target will be affected.
Create a tabular data Report, with summary totals. Including user prompt Task Parameters to specify the range of data.
|Edit/update table data directly
Select this Task Method if the purpose of the task is to edit or update table data. Validation rules may be applied but there
is no option to insert additional rows into the target table. The purpose of this task is simply to manually amend and validate existing table data.
Edit, Validate and Update multiple rows.
'update SQL' is automatically generated for you.
The range of data can be specified within 'User Task Parameters' settings.
|Edit/update/insert table data directly
This Task Method allows table data to be edited but in addition allows the creation of new records in the target table. Validation rules may be applied to data before existing data is amended or new records are created. The purpose of this task is manually amend and validate existing table data but to enable the creation of new records if required.
Edit and insert table data in the same task
SQL code to handle 'if exists update else insert' is automatically generated for you.
The range of data can be specified within 'User Task Parameters' settings
|Insert or Update table data from any source
Selecting this Task Method enables records in the target table to be updated or inserted from the specified data source. This data source may be an Excel worksheet, a CSV file, a SQL query or even the results of a stored procedure. The purpose of this task is therefore to take data from a source and update or insert this data into an existing table. Again, validation rules may be applied when defining the task.
Data may be pasted into the application or can be loaded from and data source defined using 'Data source' settings.
The data source may be a SQL query, the results of a stored procedure, or from any other data source including Excel worksheets and CSV files.
To additionally update existing rows - check the 'UPDATE existing' option in the 'Target Columns' settings.
|Send data to Stored Procedure from any source
This final Task Method enables the source data to be sent to a database stored procedure for further processing. This processing could include complex validation, multiple table updates/inserts etc. The purpose of this Task Method is therefore to make provision for data manipulation that cannot be performed using previous and the more standard Task Methods previously described.
Validate and upload data to a database Stored Procedure.
The Stored Procedure can carry out additional server validation checks and return error messages to the application.
Example: Add / Amend Book Tiles in our PUBS database
Our goal in this simple example is to ADD book titles or AMEND existing book titles in the PUBS database. We therefore enter a Task Name and description and select the task method 'Edit/update/insert table data directly' as below: