"Update if... Insert Rest" Action
Sometimes, it is necessary not only to update existing records, but also to insert new records into the same database table. For such cases, MapForce provides an "Update if... Insert Rest" action. This works as follows:
•If the Update if condition is true, then the existing database record is updated with data from the mapping.
•If the Update if condition is false, and an Insert Rest condition exists, then a new record is inserted.
•If records exist in the database with no counterpart in the source file, then these records remain unchanged.
MySQL / MariaDB ODBC note
If the target database is MySQL or MariaDB through ODBC, the option Return matched rows instead of affected rows must be enabled in the Cursor/Results tab of MySQL ODBC Connector. Alternatively, if you enter the connection string manually through the Database Connection wizard, add Option=2 to the connection string , for example: Dsn=mydsn;Option=2; |
To enable this option from the MySQL ODBC Connector:
1.Press the Windows key and start typing "ODBC".
2.Run the ODBC Data Sources Administrator (either 32-bit or 64-bit, depending on the platform of the installed MySQL ODBC Connector).
3.Click the Data Source Name (DSN) used by the MapForce mapping, and then click Configure.
4.Click Details >> to make the advanced options available.
5.Click the Cursors/Results tab, and then select the check box Return matched rows instead of affected rows.
Example
The following example shows you how to merge (both update and insert) data from an XML source into a database table. The example uses the following files:
•altova-cmpy-extra.xml — contains the source data to be inserted into the database.
•Altova_Hierarchical.xsd — the schema used to validate the instance file above.
•Altova.sqlite — the target database to be updated.
All files are available in the folder <Documents>\Altova\MapForce2023\MapForceExamples\. Below, the complete path to them will be omitted, for simplicity.
The mapping in this example modifies a sample database file. It is strongly recommended to back up the original database and start with a new copy before following the steps below. This ensures that the original examples are not overridden and that you get the same results as below. For more information, see Executing Mappings Which Modify Databases. |
The goal of the mapping is to merge all records from a source XML document into a target "Person" table. Namely, for each record in the source XML data, the mapping must do the following:
•If the person's PrimaryKey in the XML file corresponds to a person's PrimaryKey in the database, then update the record.
•Any existing records in the Person table which do not meet the above condition must not be affected.
•If the person's PrimaryKey in the XML file does not have a match in the target database table, then add a new record to the database table.
To achieve the mapping goal, we will take the steps below.
Step 1: Insert the source XML component
•On the Insert menu, click XML Schema/File, and browse for Altova_Hierarchical.xsd. When prompted to supply an instance file, browse for altova-cmpy-extra.xml.
Step 2: Insert the target database
•On the Insert menu, click Database, and go through the wizard steps to connect to Altova.sqlite. See Add a Database.
Step 3: Draw the connections
•Draw the mapping connections as shown below.
Step 4: Configure the "Update if... Insert Rest" actions
1.On the target component, click the Action: Insert ( 
) button next to the "Person" table.
2.Next to Action on record, select Update if... . This changes the database table action to a conditional update action. That is, the current record will only be updated when a condition is satisfied (see next step).
3.Next to PrimaryKey, select the value equal, as shown below. This defines the update condition: that is, the database record will be updated only when its PrimaryKey value is equal to the PrimaryKey value coming from the mapping.
4.Optionally, select the NULL equal check box. In this example, this check box is irrelevant, because neither the PrimaryKey field in the source XML nor the PrimaryKey field in the database contains any null values. However, you should select this check box if your data contains null values, and if you want to treat such null values as equal; otherwise, you may get undesired results. For more information, see Handling Nulls in Database Table Actions.
5.Click Append Action. This adds a new action to the right of the existing Update If action. Configure the new action as Insert Rest:
In the image above, the database table actions have been configured in accordance with the goals of the mapping. That is, only when the Update If... condition is satisfied will the record be updated; otherwise, it will be inserted. The option "mapped value" specifies that values from the mapping will be used to populate all fields of the record.
It is also possible to define more than two actions against the same database table (this is not necessary in this example, however). At mapping runtime, actions are executed from left to right. The last Insert action is considered final; any other actions added after it will be ignored.
Note that the Append Action button on the dialog box adds the new action after the selected one. Insert Action adds the new action before the selected one. To delete an existing action, click anywhere inside it, and then click Delete Action.
6.Click OK to close the dialog box. Notice that, back on the mapping, the Action: Insert button has now changed to an Action: Update; Insert ( 
) button. This indicates that both an update and an insert action is configured to take place for this table.
Step 5: Preview the mapping and update the database
Click the Output pane to preview the mapping. A SQL script is generated, containing actions to be executed against the database. The script has not modified the database yet; it is only for preview. INSERT statements are not visible in the preview script. This is normal behavior, because records are inserted conditionally, and the INSERT statements depend on the result of the Update If action (which is not known before the mapping runs).
Note: For certain database types, MapForce creates MERGE statements instead of UPDATE statements. For further information, see MERGE statements.
To run the script against the database:
•On the Output menu, click Run SQL-Script.
Now that the mapping has been executed and the script applied against the database, notice that INSERT statements are visible in the Output pane.
| Note: | Running the SQL script directly from MapForce is just one of the ways to update the database, see also Executing Mappings Which Modify Databases. |
If you open the "Person" table in the DB query pane of MapForce (see Browsing and Querying Databases), you can see the result of the mapping as follows:
•All database records which had corresponding primary keys in the XML file have been updated. Examples are records with primary key 1, 2, 3, 4, and 5.
•All database records which had no corresponding keys in the XML file remained unaffected. Examples are records with primary key 6, 7, 8, and 9.
•New records have been inserted to the "Person" table (where key did not already exist in the database). Examples are records with primary key 30 and 31.
