Automation Action: Update A Database Using Extracted Fields
Automatically update a SQL database using extracted fields parsed from the incoming message.
Update a database with fields extracted from the incoming message.
This action can be used to automatically insert or update a record in a database based on the Extract Field actions defined in your Automation. The tables and column names used in the SQL commands are specified on the Database Update tab on each individual Extract Field Action. ThinkAutomation will then create the necessary SQL commands automatically.
Select a Database Type to connect to from the list. You must specify a Connection String that ThinkAutomation will use to open the database. Click the
... button to build the connection string. Click the Test button to verify that ThinkAutomation can connect to the database.
The UPDATE and SELECT commands will only be created if you have defined one or more of your Extracted Fields as Key Fields. ThinkAutomation will then first check if a record exists with the key field values by issuing a SELECT * FROM ... command. It will then execute the UPDATE command if a record is found or the INSERT command otherwise.
You can have only have one Update A Database Using Extracted Fields Action in your Actions List since the SQL statements are automatically created based on your Extract Field actions. If you want to update multiple tables within the same database you can specify different table names against each Extract Field action in the Update Table Name entry - ThinkAutomation will then create separate SQL commands for each separate table being updated. You need to add your Update A Database Using Extracted Fields action below your Extract Field actions to ensure each extracted field has a value.
The result of the update can be assigned to a variable. Select a variable from the Assign Result To list. The variable will receive either 'Inserted','Updated' or an error message if the update failed.
If you need to update multiple separate databases within the same Automation then you can use the Update A Database Using Custom SQL Action. You can have any number of Update A Database Using Custom SQL Actions within your Automation.