MapForce Supports SQL Merge When It’s the Right Tool for the Job

Large database tables can easily contain a million, even hundreds of millions of rows of data. Database administrators and others charged with maintaining such large datasets are always concerned about execution time for ETL (Extract, Transform, and Load) operations, updates, and other SQL queries. To make these operations more efficient, some — but not all — database vendors implemented a SQL merge statement to insert or update rows of an existing table as a single bulk-insert statement rather than requiring individual statements for each row.

Altova MapForce automatically supports SQL merge when it is available for the target database. Let’s look at an example.

Insert vs. Update-If

The image below illustrates a data mapping from elements of an XML file to a database table.

By default, MapForce would generate a SQL insert statement for each Person element in the source file, which is fine for populating rows of a new, empty database table. In other situations, such as synchronizing a backup copy with a live dataset, insert statements are not appropriate. MapForce conveniently lets you specify the correct SQL operation you need for each database mapping.

Simply click the Action icon next to the target database table, which is the Person table in the example above, to open the MapForce Database Table Actions dialog. The screenshot below illustrates the dropdown feature that lets you change the SQL Insert statement to Update-If.

Update-If needs a condition to be set. In this mapping we want to update records in database where the source data includes an existing primary key. And, we want to insert a new row where the primary key in the source data does not already exist in the database.

This is commonly known as an Update If…Insert Rest operation.

The Database Table Actions dialog lets us define the condition to be tested for update and an alternative action taken when the condition is false. The completed set of actions defining Update If…Insert Rest is shown here:

Update vs. Bulk Merge

So far we have not even mentioned the vendor of the database for the target table of our mapping. That’s because the mapping is defined the same way for all popular databases supported by MapForce. We don’t need to do anything different whether the target supports SQL merge or not.

When we click the MapForce Output tab below the database mapping design, MapForce generates a SQL script customized for the particular database type. This script is then displayed for review in the MapForce output window. It is NOT executed automatically.

If the target database supports SQL Merge, you will see a MERGE statement near the top of the script.

If the target database does not support SQL Merge, you will see individual UPDATE…WHERE statements for each row of source data.

You can execute the script directly in MapForce by choosing the Run SQL Script option from the MapForce Output menu, or you can save the mapping as an execution file and automate it in MapForce Server with input file names specified at runtime.

If SQL Merge is implemented by the target database, merge statements will be generated regardless of whether the mapping is executed directly by MapForce or automated via MapForce Server. MapForce 2017 supports SQL Merge statements for SQL Server 2008 and later, Oracle, IBM DB2, and Firebird.

Tests here at Altova indicate bulk execution of SQL Merge by MapForce Server can be up to 15 times more efficient than previous techniques.*

Click here for more information on database mapping with MapForce including a complete list of databases supported as data mapping sources or targets, or click here to download a free fully-functional 30-day trial. The files used for the mapping in this blog post are provided as MapForce examples, and detailed instructions to recreate the mapping are in the integrated MapForce Help.

*Performance results based on Altova internal tests. Your results may vary.