MERGE Statements
For certain mappings which both update and insert data into a database table (see also "Update if... Insert Rest" Action), MapForce generates MERGE statements to be executed against the database at mapping runtime. The execution engine may not necessarily be MapForce, see Executing Mappings Which Modify Databases.
MERGE statements are supported for the following database types:
•SQL Server 2008 and later
•Oracle
•DB2
•Firebird
MERGE statements reduce the number of database server calls, since they combine the INSERT and UPDATE statements into one. Also, in case of MERGE statements, the consistency check is done by the database.
To see whether the mapping will execute database MERGE statements at runtime (as opposed to applying a combination of INSERT and UPDATE statements):
1.Create a mapping which uses an Update if... as well as an Insert Rest action. For an example, see "Update if... Insert Rest" Action.
2.Preview the mapping, by clicking the Output pane.
If MERGE is supported by the database type, the generated SQL script includes MERGE statements, for example:
If MERGE is not supported by the database type, the generated SQL script includes UPDATE statements only. No INSERT statements are visible for preview, since those are to be executed only if the Update If... condition is not satisfied (and this is not known before the mapping execution).
Notes
•MapForce creates MERGE statements automatically when it detects a supported database type; it is not possible to manually influence whether MapForce should create a MERGE statement.
•If you are updating multiple tables having parent-child relationships, merges are created only for "leaf" tables. A "leaf" table is the deepest child table that is mapped. For example, in the mapping below, Update If... Insert Rest... actions have been defined for both Users table and Addresses table. However, MERGE statements will be generated only for the leaf table, Addresses. The parent table, Users, gets UPDATE or INSERT statements instead of MERGE.
•With MERGE statements, the "Bulk Transfer" option (see Bulk Inserts (MapForce Server) is supported only for ODBC and JDBC database connections.