The Merge Data command merges fields from a Source database into the corresponding fields of a Target database.

Note: if you simply want to add new records after the existing records of the current database, you should generally use the Append Data command instead of the Merge Data command.

Select Merge Data (or click Merge from the ribbon in spreadsheet edit) to open the Merge Data Library. Select an existing set of Merge Data parameters, or click New to create a new set of parameters.

Each entry must have a Name in order to be saved in the library.

The Target database represents the database that will receive update data. This may be set as the "<Current database>" for cases in which you want ProVal's current database to receive the update data. The Source database is the database file which contains the data to be merged into the target database.

To select only some of the records in the source database, enter a Selection expression to apply to the source database. If you leave the Selection Expression box blank, then all records will be merged. Press the F1 key when your cursor is in this box to get a list of the operators you can use to create the expression.

Select one or more Key fields. If you choose a character field, check Match case in keys if you wish to treat case differences as significant (so “Joe Smith” and “JOE SMITH” will be treated as different key values).

Alt Key is an optional parameter that can be used to specify a secondary key field (or fields). When a matching record cannot be found based on the primary key field(s), a second check is performed using the alternate key. This can be helpful in situations where employee ID numbers are corrected from one year to the next. As an example, consider a current year database with primary key field EEID and alternate key field OLDEEID. When matching EEID in the current year database against the imported data, a second step will be taken for any for any unmatched records. Those unmatched records in the current year will also have the alternate key field OLDEEID checked for possible matches against EEID in the imported data.

Select one or more source fields to read (to import into the target database) from the list of all fields in the source database. An asterisk (*) marks fields that are defined in both the target database and the source database. Click the All or None buttons to select all or none of the fields in the source database, respectively. By default, fields in the source database are used to update identically named fields in the target database. To choose different names for the imported fields, click the Target Flds… button.

The Target Fields dialog box lists the Fields in Source Database that you’ve chosen to import in the first column of the spreadsheet. The second and third columns lists the Fields in Target Database into which these fields will be mapped. Choose whether to map fields into Existing Fields by selecting a field which already exists in the Data Dictionary from the drop down, or choose to map into a New Field in the target database by naming that new field in the third column of the spreadsheet.

The last onscreen options dictate how the data update should be performed. The first option specifies what should be done for records that were found in both files (i.e., records that were "matched" based on value(s) of the key field(s)). Select, for records found in both files, if you want to overwrite:

• All values. Data from the source database will REPLACE existing data in the target database. Check Allow values to be overwritten with missing values in order to update the target database with missing values from the source database. If unchecked, missing values in the source database will not replace non-missing data in the database. That is, existing (i.e., non-missing) source data will always overwrite target database data. 

• Only missing values. Existing non-missing values in the database will not be altered, but any "holes" in the database (i.e., missing values) will be filled in with data from the source database.

• Nothing. Neither missing nor non-missing data in the target database will be updated (i.e., records that already exist in the target database will be unchanged). This option is uncommon but could be useful when you are using the merge process only to add new fields or records to the database.

The second option specifies what should be done with the records themselves (i.e., what records should be preserved in the merged target database). Select, in the merged target database, whether you want to keep records if present in:

• The target database. Records found ONLY in the source database will be ignored, and records present in the target database but NOT in the source database will remain in the target database.

• The source database. Records found ONLY in the source database will be appended to the target database, and records present in the target database but NOT in the source database will be deleted.

• Both databases. Records found ONLY in the source database will be ignored, and records present in the target database but NOT in the source database will be deleted.

• Either database. Records found ONLY in the source database will be appended to the target database, and records present in the target database but NOT in the source database will remain in the target database.

Lastly, the option to Create MergeStatus field allows you to specify whether you want a "merge status" field in the database. This coded field, which will be named MergeStatus, classifies each record as one of:

• Matched: found in both the target and source databases
• Not Matched: found only in the target database, or
• Appended: found only in the source database and added to the target database

Click the Run button to perform the merge. When the merge is complete, the system displays a summary of what was done and asks for confirmation before saving the changes in the file. The summary includes the following information:

• Number of records in the database.
• Number of records in the source database and, if you provided a selection expression, the number of selected records.
• Number of records found in both files (i.e., matched records).
• Number of records updated for each field. This is the number of records for which the source database contained data different from that in the target database.
• Number of records found only in the source database, and the number that were appended to the target database. Only records satisfying the selection expression are counted.
• Number of records found only in the target database, and the number that were deleted.

Click “Yes” to save the changes, or click “No” to discard the changes and return to the Merge Data parameters. If you choose “No”, the merge will be canceled, but any new fields that were created (and filled with missing values) during the merge will still exist. You can erase these fields, if you wish, by using the Database > Edit Data > Delete Fields command.

Note: The Merge Data command produces large amounts of unused space in the database file, even if you choose not to save the changes made in the update. You can eliminate the unused space by running the File > Pack Files command.