Using the Insert or Update Step

Unknown

Using the Insert or Update Step

Unknown

I was wondering if someone could supply an example of how and when to use the Insert or Update step that is exposed on a database table. Im assuming this step can compare an object passed to it and make some sort determination as to whether an existing DB record should be updated or a new DB record should be created, but I cant find it documented anywhere. It seems like it would be extremely useful in creating an ETL flow.

Unknown

[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]Hi Dennis,[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]Insert or Update Step is combining both insert and update functionality together. If you want to use this step for insert functionality you have to define primary key different than the existing table primary key values. This will add a new row to the table. If you want to update an existing row you need to define that rows primary key in the input area.
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]In order to see the table fields as input areas in your Insert or Update Step properties, you should select the input area as Build Array and select the first array as Build Data. This setting will give you all the table fields. You can select the necessary fields from here. In both update or insert functionality, you should always include the primary key field. [/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]I attached an example to demonstrate this combine behavior. In my example, I have an entity_account table with some account information. The primary key of this table is Account Id. So, in order to add a new account to the table, I am filling the Account Id input field with a unique new id. If I want to update an existing row, I need to fill Account Id field with respective account id. I put a form with a data grid to facilitate viewing table changes. [/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]In addition to the example, the below documentation can be helpful. Please check example/documentation and if you have any question please let me know. [/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif][url=https://documentation.decisions.com/docs/integrate-with-database-table]Integrate with Database Table[/url][/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif][url=https://documentation.decisions.com/docs/use-flow-steps-integrate-external-database]Use Flow Steps to Integrate External Database[/url][/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]Regards[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif][img]att1[/img][/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif][img]att2[/img][/font][/color]
[i]edited by kenan@decisions.com on 6/25/2019[/i]