Error Updating PK ID

Document created by mike_c_frazier Employee on Dec 10, 2012
Version 1Show Document
  • View in full screen mode
Multiple Statements in the same Database Operation does not allow for a write statement to insert the same PK ID into a child destination table if it is auto-generated from the SQL insert of the first (parent) statement as a primary key
For both parent/child table records to have the same account ID, implementing Multiple Statements in one operation is likely not working because the created ID can NOT be retrieved in the same SQL operation.
He are some options to consider instead:

     1. Remove the auto-create id from the database table and instead have Boomi auto-increment the data using a Sequential Value map function. This is the optimal solution if you have the option of removing the auto-generated function from the parent database table.

http://help.boomi.com/display/BOD/Map+Functions#MapFunctions-NumericFunctions

     2. Create two separate branches, insert in the parent table in the first branch. Then in the second branch, lookup the id from the parent table and prepare a separate database profile to insert the data into the child tables. This option may not be optimal depending on your performance requirements.

     3. Create two separate branches, insert in the parent table in the first branch. Create a stored procedure to obtain the id from the parent table based on the inputs and insert the data into the child tables. This stored procedure can be called in a separate database profile to write to the child tables. This option may be better for performance than option #2.

Attachments

    Outcomes