Like what you see? Have a play with our trial version.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Note

In rarer circumstances, the target tables may not exist in advance. In these cases Yellowfin can create the table for you, provided you have the right security access. The Create if Not Exists option will create the table if it does not exist. Typically this will only happen the first time a flow is run, after which the existing table will be used. The Drop Existing will always delete and re-create the table, thus deleting any data that may have been stored in that table - and should be used with caution. Similarly the Drop if Meta-Data Conflict will drop and re-create the table if there is a mismatch between the table structure and the data contained in the flow. This option is also destructive to data and should be used with caution.

 

 

Styleclass
ClasstopLink

top

 


Anchor
updateoptions
updateoptions

...

Update Type

Description

Insert

To append or add data to the end of the existing data.

Truncate

To replace all the existing data with the new values. This should be used with caution as it will delete any existing data.

Update

Update the value of fields in the database table where a matching row exists in the transformation flow. This option overwrites existing data and should be used with caution.

Update & Insert

Update the value of fields in the database table where a matching row exists in the transformation flow. This option overwrites existing data and should be used with caution. If no matching row exists then insert a new row.

 

 

Styleclass
ClasstopLink

top


Step Configuration

Once you are ready to store your transformed data into an SQL output step, follow the instructions below:

...

  1. Click on the Output Steps button on the left side of the Transformation Flow builder, to view a list of output steps.
  2. Drag the Output to SQL Database step from the list. 



  3. Click on the step to bring up in configuration details in the Transformation Flow panel.
  4. Then select a database from the list. Only writeable data sources will be shown.
  5. Choose a table creation option. Click here to understand the table creation options.



  6. Continue with the selected process:
    1. If Use Existing was selected, select a pre-existing table to write data into. Then skip to step 4. 

      Image Added
    2. If Create If Not Exists was selected, then enter a new table name. You can optionally select any table fields that you would like to index (note: indexes are used to improve query performance. If in doubt about this option, please contact your system administrator). Move on to step 4.
      Image Removed 

      Image Added

    3. If Drop Existing was selected, then enter a new table name. Skip ahead to step 5.Choose any field to index it, if required. (Then skip the next 2 steps of this guide.)
    4. If Drop if Metadata Conflicts was selected, then enter a new table name, also specify whether the existing table is to be dropped in case there are more fields in the transformation flow than in the table. Then skip to step 4.   

      Image Added

  7. Choose an update type to indicate how the existing table will be updated. Click here to understand all the options.

    note


    Image Added

  8. For Update and Update & Insert options, additional row matching is required. This is done by configuring a Where clause to specify which fields should be updated.

    Note

    When updating existing records in the output database, you will need to specify two types of columns/fields:

    • A join field: This is a field that joins the flow table with the database table. This field should contain matching values in both the tables. The join option is used with this field.

    • A set field: This is a field whose value will be used to update the database.  The set option is used with this field.

    choosing Join for the identifier or matching field, and then Set to indicate which field to update. If no option is selected, the field will not be used in the join or be updated as part of the flow

    Image Added

    1. Choose the Join option for this column.

    Image Removed


    1. Image Added

    2. Then select the field with which to update the output database, and choose the Set option with it (this ensure that the database column will be replaced with the values of this field).

      Image Added

  9. Switch on the Run in design mode toggle to run this output step in design mode. SEE WARNING NOTE.



  10. Once the configuration is done, click on the Apply button to save these changes.

 

Styleclass
ClasstopLink

top