Have Questions?  Need Expert Answers?  JOIN LUNCH N' LEARNS!

Bulk Insert to [Database Name]

Prev Next


Step Details

Introduced in Version3.0.0
Last Modified in Version8.2.0
LocationIntegration > Database > [Database Name] > [Table Name]

For an External Database Integration, multiple entities can be added to a table using the Bulk Insert to [Database Table Name] step. 

This step operates similarly to a Batch Insert step, allowing users to add multiple instances of a specific DataType that are either mapped from another source or mapped directly on the step via Constant mapping to a Database.

However, this step can only insert a specific External Database Table with the Insert Property enabled. 

In v7.13+ and v8.4+, if a PostgreSQL database is used, the Serial Primary Key column will be generated and auto-incremented when a new value is added to the table.

Oracle Databases
This step does not function for Oracle Databases.
MySQL
This step is not currently available for MySQL integrations.




Properties

Environment

PropertyDescriptionData Type
Use Transaction DataIf enabled, creates an additional input for a Transaction Data data type to be included with the insert.Boolean
Run in Defined EnvironmentEnabled by default. Runs the query in the defined environment when setting up the table integration.Boolean
Override Connection StringOverrides the connection string used by the step.Boolean

Inputs

PropertyDescriptionData Type
Inserted ObjectsThe object being inserted into the database.Varies

Outputs

PropertyDescriptionData Type
Error DetailsOutput displayed when the step fails. This will contain details on why the transaction failed to complete String
Error MessageOutput displayed when the step fails. This will contain the exact error message as to why the transaction failed to complete String



Enabling Bulk Insert For External Databases

To use the Bulk Insert step for an External Database Table, the Insert Property must first be enabled for the Table. To do so either enable it from the Define Flow Steps window upon adding the Table Integration or:

  1. From the Decisions Studio, navigate to System > Integrations > Databases [NAME OF DATABASE]
  2. Right-click the desired Table, then click Edit Table
  3. Click NEXT through each window until the Define Flow Steps window appears. 
  4. Under Define Flow Steps, check the Insert Boolean
  5. Click SAVE to save the changes and close the window.



Example 

The following example demonstrates using the Bulk Insert step to add entities (in this case Accounts) to a connected PostgreSQL Database

  1. From a Designer Project, create or update an existing Flow
  2. From the Flow Designer, in the Toolbox tab, navigate to Integration > DATABASE > [NAME OF DATABASE] > [NAME OF DATABASE TABLE], and attach a Bulk insert to [Database Name] step to the workspace. 
  3. Connect the step to the Start and End steps. 
  4. From the Properties tab of the Bulk Insert to step, under INPUTS > Inserted Objects define the items which will be added to the Table. In this case, mapping was set to Constant, and values were manually added.

    If a list of entities needs to be passed as inputs to the step, they must use the datatype generated for the Table, which can be found under the Inputs section in the Properties panel. In this case, it is PostGresTest_publicaccounts. 
  5. Save the Flow, then Debug. If the inputs are valid, the Flow will travel down the Inserted path and the Table will be updated.

Related Information

Forum Posts: