Skip to Content

Use a database as a target in Jitterbit Design Studio

When a transformation uses a database as its target the Jitterbit Integration Environment steps you through a wizard that defines one or more Insert or Update SQL statements that the server will execute via ODBC to manipulate data in the database. Database targets are defined using the following steps.

Search and select database objects

This lets you select the target tables and views for which data will be manipulated. You can search multiple times and select multiple database objects (tables and views) in a single transformation. When searching objects please note that the search is no longer case-sensitive, as of version 8.19.

If you have a large number of objects in your database, search using a string to identify the table that will be the search target. This helps prevent timeouts as it keeps the system from attempting to fetch all objects. Some non RDBMS ODBC drivers do not support the ability to search on objects e.g. Microsoft Access and Microsoft FoxPro. Typically these types of databases do not store many objects, so search on all objects without entering a search criterion for them.

If you select one object:

You will be prompted to select the mode of operation. An operation can have one of the following modes:

  • Insert that will instruct Jitterbit to perform inserts only to the target table

  • Update that will instruct Jitterbit to perform updates only to the target table using the identified key to identify the record that requires updating.

  • Insert/Update (Upsert) that will first check to see if the record with the identify key exists. If it does, the system will update, otherwise it will insert.

If you select multiple objects:

Specify the number of times each object will be used.

In certain cases, you may need to use a table multiple times e.g. when inserting into a company table, your source may include customer, supplier and partner. Those are three different companies within one record in the source data. In this case you would choose to use the company table three times.

You will need to create the parent-child (one-many) relationships between objects.

Select parent tables and columns and child tables and columns to join on. This is needed to enforce transactional integrity of an object as well as to derive the foreign key for a child table.

Define the type of relationship between these objects.

This allows you to define constraints if required:

  • 1 to N tells Jitterbit that every parent record must have 1 or more child records.

  • 1 tells Jitterbit that every parent record only has one child record and Jitterbit will only process the first child record it selects.

  • 0 to N tells Jitterbit that every parent record can have 0 or more child records.

Select the mode of operation.

An operation can have one of the following modes:

  • Insert that will instruct Jitterbit to perform inserts only to the target table

  • Update that will instruct Jitterbit to perform updates only to the target table using the identified key to identify the record that requires updating.

  • Insert/Update that will first check to see if the record with the identify key exists. If it does, the system will update, otherwise it will insert.

Once you have completed defining your database target, a tree data structure should appear in the target transformation tree to depict what you have defined. In mapping to your database target, if you have a multi-table target with parent child relationships and you are inserting data, you should become familiar with using the transformation functions <SQLIDENTITY> and <SEQUENCE>.

<SQLIDENTITY> is used for all target databases that support identity or automatic number generating keys. Once the parent is inserted then 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 and should be used for Oracle.