Merging Data

The SaveToDB add-in supports a merge mode that executes the merge operation for each row in an Excel table. This feature is handy for saving data loaded from the web to a database.

Users can run the Merge Wizard once and then repeat the merging of refreshed data using the Merge menu item. Developers can also configure this feature so that the add-in performs the merge operation when a user clicks the Save button.

There are four ways to implement data merging:

• Merging to a table
• Merging to a view
• Merging using stored procedures
• Merging using SQL code

Configuration

You can configure merging in the xls.objects table or through the query list views.

Here’s a sample entry for the xls.objects table:

Here’s a sample entry for the query list view:

Use the xls.objects table to configure the default behavior. Use the query list views to create automatic configurations using SQL.

To use a query list, SaveToDB users must select it in the Connection Wizard dialog box.

The following sections provide examples for the xls.objects table.

Merging Data to Tables

To merge data into a table, specify it as an INSERT object.

For example:

Ensure that the source data includes columns that match the primary keys in the target table. You can create the necessary source columns using Excel formulas.

Merging Data to Views

This process is similar to merging data to tables.

For example:

The view must be updatable.

Merging Data using Stored Procedures

To merge data using a stored procedure, specify it as an INSERT object.

For example:

Merge procedures can accept parameters like other edit procedures. See Saving Data Using Stored Procedures for more details.

Merging Data using SQL

You can use SQL code in the INSERT object fields to merge data.

For example:

This approach is useful for databases like SQLite that do not support stored procedures. The SQL code can also use parameters. See Saving Data Using SQL for more information.