Database insert activity

Introduction

A Database Insert activity inserts new data into a Database endpoint and is intended to be used as a target to consume data in an operation or to be called in a script. After configuring a Database connection, you can configure as many Database activities as you like for each Database connection.

Create a database activity

An instance of an activity is created from a connection using an activity type.

To create an instance of an activity, drag the activity type to the design canvas or copy the activity type and paste it on the design canvas. For details, see Creating an activity instance in Component reuse.

An existing activity can be edited from these locations:

• The design canvas (see Component actions menu in Design canvas).
• The project pane's Components tab (see Component actions menu in Project pane Components tab).

Configure a database insert activity

The steps for configuring a Database Insert activity differ depending on whether you need to join multiple objects:

• To insert into a single object, without joins, refer to Single-object insert.
• To insert into multiple objects, with joins, refer to Multiple-object insert.

Single-object insert

Follow these steps if the database insert is to a single object, and you do not need to create any joins with other objects. If you need to join multiple objects, refer instead to Multiple-object insert.

• Step 1: Enter a name and select an object
• Step 2: Review the data schema

Step 1: Enter a name and select an object

• Name: Enter a name to use to identify the Database activity. The name must be unique for each Database Insert activity and must not contain forward slashes (/) or colons (:).

• Provide the Table Names Reference: This section displays the tables available in the Database endpoint using any applied filters, after using the refresh icon for the initial loading of objects. If there are a large number of objects in the database, it is recommended to filter the objects using View or Search prior to refreshing.

  • View: Use the dropdown to filter the list of displayed objects to one of "Tables, Views", "Tables", "Views", or "System Tables". By default, the view "Tables, Views" is shown.

  • Search: Enter any column's value into the search box to filter the list of objects. The search is not case-sensitive. The list of objects may be filtered automatically or you may need to refresh the list, depending on whether the objects have already been retrieved:

    • If searching on objects that have already been retrieved from the database and already appear within the list, any search terms typed within the search box automatically filter the list.

    • If searching on objects that have not yet been retrieved, for example when attempting to limit the number of objects retrieved for databases with a large number of objects, use the refresh icon next to the search box to fetch the objects from the database using the filter.

    Note

    For databases that do not support searching on objects, such as Microsoft Access, no results are returned.

  • Refresh: Click the refresh icon to load or reload tables from the Database endpoint. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects to the database, or if you have additional search criteria that you wish to apply prior to retrieving database objects.

  • Include the Schema/Owner in the Table Names: Select this checkbox to include the schema in the table names. This may be useful if you use the same table name in multiple schemas (such as for development and production) to allow you to see and select the table in the correct schema.

  • Provide the Table Names Reference: The list of objects is displayed in a column on the left. Click a single object to add it to the table on the right. If you are inserting into multiple objects that need to be joined, refer to Multiple-object insert.

    Tip

    If the list does not populate with available objects even after applying a filter, the Database connection may not be successful. Ensure you are connected by reopening the connection and retesting the credentials.

  • Table of Selected Objects: The selected object is displayed in a table on the right. The columns in this table are not relevant for single-object inserts and cannot be edited, except to remove the object (hover over the far right column and click the remove icon). If you are inserting into multiple objects that need to be joined, refer to Multiple-object insert.

  • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click to close the configuration without saving changes made to any step. A message asks you to confirm that you want to discard changes.

Step 2: Review the data schema

• Data Schema: The target data schema is displayed. If the operation uses a transformation, the data schemas are displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

  Note

  Data is transferred as it is provided. If there are data length mismatches between source and target fields, you can use String functions to trim the data.

  Note

  To refresh a data schema in an existing activity, you must advance through each activity configuration step again, making at least one change (such as adding and removing a character from the end of the name of the activity) to force a refresh of the schema.

• Back: Click to temporarily store the configuration for this step and return to the previous step.

• Finished: Click to save the configuration for all steps and close the activity configuration.

• Discard Changes: After making changes, click to close the configuration without saving changes made to any step. A message asks you to confirm that you want to discard changes.

Multiple-object insert

Follow these steps if the database insert is to multiple objects and you need to create joins between them. To insert only into a single object, without joins, refer instead to Single-object insert.

• Step 1: Enter a name and select objects
• Step 2: Review the data schema

Step 1: Enter a name and select objects

• Name: Enter a name to use to identify the Database activity. The name must be unique for each Database Insert activity and must not contain forward slashes (/) or colons (:).
• Provide the Table Names Reference: This section displays the tables available in the Database endpoint using any applied filters, after using the refresh icon for the initial loading of objects. If there are a large number of objects in the database, it is recommended to filter the objects using View or Search prior to refreshing.

• View: Use the dropdown to filter the list of displayed objects to one of "Tables, Views", "Tables", "Views", or "System Tables". By default, the view "Tables, Views" is shown.

  • Search: Enter any column's value into the search box to filter the list of objects. The search is not case-sensitive. The list of objects may be filtered automatically or you may need to refresh the list, depending on whether the objects have already been retrieved:

    • If searching on objects that have already been retrieved from the database and already appear within the list, any search terms typed within the search box automatically filter the list.

    • If searching on objects that have not yet been retrieved, for example when attempting to limit the number of objects retrieved for databases with a large number of objects, use the refresh icon next to the search box to fetch the objects from the database using the filter.

    Note

    For databases that do not support searching on objects, such as Microsoft Access, no results are returned.

  • Refresh: Click the refresh icon to load or reload tables from the Database endpoint. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects to the database, or if you have additional search criteria that you wish to apply prior to retrieving database objects.

  • Include the Schema/Owner in the Table Names: Select this checkbox to include the schema in the table names. This may be useful if you use the same table name in multiple schemas (such as for development and production) to allow you to see and select the table in the correct schema.

  • Provide the Table Names Reference: The list of objects is displayed in a column on the left. Click objects one at a time to add them to the table on the right. Any combination of tables, views, and/or system tables may be selected. If instead you need to insert into a single object without joins, refer instead to Single-object insert.

    Tip

    If the list does not populate with available objects even after applying a filter, the Database connection may not be successful. Ensure you are connected by reopening the connection and retesting the credentials.

  • Table of Selected Objects: The selected objects are displayed in a table on the right. Each column is detailed below:

    

    • Table: The name of the selected object, a table, view, or system table.

    • Parent: On each child object being joined, use the dropdown to select the name of the object that should be the parent. This is needed to enforce transactional integrity of an object as well as to derive the link key(s) for a child object.

    • Join Type: On each child object being joined, a dropdown becomes available after you have assigned Link Keys (covered next). Use the dropdown to set the type of relationship between the selected objects:

      • One or More: This join type requires that every parent record has at least 1 child record, and possibly more child records.
      • One Only: This join type requires that every parent record has 1 and only 1 child record.
      • Zero or More: This join type allows every parent record to have either 0 or more child records.
      • Zero or One: This join type allows every parent record to have either 0 child records or only 1 child record.

    • Link Keys: On each child object being joined, once an object is selected as the Parent, a red Assign link appears within this field. Click Assign to open a separate window where you assign the link keys:

      

      • Parent Object: The parent object and list of fields is displayed in the left column. Any fields used as primary keys within the database are indicated by a key icon. If you have a large number of fields, you can use the search box to filter fields by name.

      • Child Object: The child object and list of fields is displayed in the right column. If you have a large number of fields, you can use the search box to filter fields by name.

      • Assign Link Key(s): To assign a field as a link key, drag a field from the parent object on the left to a field within the child object on the right, or vice versa. To assign multiple link keys, repeat this process as needed.

      • Unassign Link Key(s): To unassign link keys, click any field that has already been linked. The linked fields become deselected.

      • Start Over: To clear all assigned link keys, click Start Over located below the parent list on the left.

      • Finish: Click Finish to save the assigned link keys and close the window. Under Link Keys, you should now see the number of assigned link keys, which you can click to reopen the link key assignment window. In addition, the Join Type dropdown becomes available for you to set the type of relationship (cardinality) between the selected objects:

        

    • Remove: To remove a selected object from the table, hover over the far right column and click the remove icon.

    • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
    • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
    • Discard Changes: After making changes, click to close the configuration without saving changes made to any step. A message asks you to confirm that you want to discard changes.

Step 2: Review the data schema

• Data Schema: The target data schema is displayed. If the operation uses a transformation, the data schemas are displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

• Back: Click to temporarily store the configuration for this step and return to the previous step.

• Finished: Click to save the configuration for all steps and close the activity configuration.

• Discard Changes: After making changes, click to close the configuration without saving changes made to any step. A message asks you to confirm that you want to discard changes.

Next steps

After configuring a Database Insert activity, you can use it within an operation or script as described below. You may also want to configure chunking to split the data into smaller chunks for processing.

Complete the operation

After configuring a Database Insert activity, complete the configuration of the operation by adding and configuring other activities, transformations, or scripts as operation steps. You can also configure an operation's operation settings, which include the ability to chain operations together that are in the same or different workflows.

Once a Database Insert activity has been created, menu actions for that activity are accessible from the project pane in either the Workflows or the Components tabs, and from the design canvas. See Activity actions menu for details.

Database Insert activities can be used as a target with these operation patterns:

• Transformation pattern
• Two-transformation pattern (as the second target only)

Other patterns are not valid using Database Insert activities. See the validation patterns on the Operation validity page.

Within a transformation, if using the option to mirror a schema provided by a Database activity using a single table (without joins), the resulting mirrored schema is automatically created with an additional node named row. When fields within this additional node are mapped to, the node becomes a loop node to allow all records to be looped through (see Loop nodes under Nodes and fields).

In addition, if joining database tables, note that the values of a child table's joined fields default to the values of the parent table's joined fields. A child table's joined fields cannot be mapped to.

When ready, deploy and run the operation and validate behavior by checking the operation logs.

Using database activities in scripts

Database Insert activities can also be referenced in a script for use with script functions that use a database target as a parameter, including these:

• DBLoad
• DBWrite

For more details on referencing activities in scripts, see Endpoints in Jitterbit Script.

In addition, you may be able to use additional database functions in the transformation. For example, in mapping to a database target, if you have a multi-table target with parent-child relationships and you are inserting data, you can use the transformation functions <SQLIDENTITY> and <SEQUENCE>.

• <SQLIDENTITY> is used for all non-Oracle target databases that support identity or automatic number generating keys. After the parent is inserted, the child can inherit the generated number from the parent by mapping this function to the foreign key in the child table.
• <SEQUENCE> performs a similar action with Oracle databases.

Use chunking

When using chunking on an operation where the target is a Database activity, note that the target data is first written to numerous temporary files (one for each chunk). These files are then combined to one target file, which is sent to the database for insert/update.

If you set the Jitterbit variable jitterbit.target.db.commit_chunks to 1 or true when chunking is enabled, each chunk is instead committed to the database as it becomes available. This can improve performance significantly as the database insert/updates are performed in parallel.

For instructions and best practices on using chunking, see Operation options.