Merging Data
Special offer: 50% discount ($1440) for ODataDB Enterprise until June 24, 2024

Merging Data

The SaveToDB add-in supports a merge mode when it executes the merge operation for every row of an Excel table.

You can use this, for example, to save data loaded from the web to a database.

Users can run the Merge Wizard once and then repeat merging refreshed data using the Merge menu item.

Developers can configure this feature. In this case, the add-in executes the merge operation when a user clicks the Save button.

There are four ways to implement merging data:

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

Configuration

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

Here is a sample for the xls.objects table:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 CRMContactsHTTP<API call>dbo.contacts

Here is a sample for the query list view:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
 CRMContactsHTTP<API call>dbo.contacts

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 topics below show samples for the xls.objects table.

Merging Data to Tables

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

For example:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 CRMContactsHTTP<API call>dbo.contacts

The source data must have columns used as primary keys in the target table.

You can create the required source columns using Excel formulas.

Merging Data to Views

This case is similar to the previous one.

For example:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 CRMContactsHTTP<API call>dbo.view_contacts

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:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 CRMContactsHTTP<API call>dbo.usp_contacts_merge

Merge procedures can use parameters like other edit procedures. See Saving Data Using Stored Procedures.

Merging Data using SQL

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

For example:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 CRMContactsHTTP<API call><MERGE SQL>

For example, you can use this with SQLite or SQL Server Compact that do not support stored procedures.

The SQL codes can use parameters. See Saving Data Using SQL.