MERGE command
Combines records from two sorted Analytics tables with an identical structure into a new Analytics table that uses the same sort order as the original tables.
Syntax
MERGE {ON key_fields|PKEY primary_key_fields SKEY secondary_key_fields} <IF test> TO table_name <LOCAL> <OPEN> <WHILE test> <FIRST range|NEXT range> <APPEND> <PRESORT> <ISOLOCALE locale_code>
Parameters
Name | Description |
---|---|
ON key_fields | PKEY primary_key_fields SKEY secondary_key_fields |
Note Only character fields, or character computed fields, can be used as key fields in MERGE.
Sorting requirementThe key fields in the primary and secondary tables must both be sorted in ascending order. If one or both key fields are unsorted, or sorted in descending order, the MERGE command fails. You can use PRESORT to sort the primary key field. If the secondary key field is unsorted, you must first sort it in a separate sort operation before performing the merge. Indexing instead of sortingThe primary and secondary tables can be indexed instead of sorted. With large tables, indexing instead of sorting may reduce the time required to merge the tables. |
IF test optional |
A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT). |
TO table_name |
The location to send the results of the command to:
|
LOCAL optional |
Saves the output file in the same location as the Analytics project. Note Applicable only when running the command against a server table with an output file that is an Analytics table. |
OPEN optional |
Opens the table created by the command after the command executes. Only valid if the command creates an output table. |
WHILE test optional |
A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached. |
FIRST range | NEXT range optional |
The number of records to process:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. |
APPEND optional |
Appends the command output to the end of an existing file instead of overwriting it. Note You must ensure that the structure of the command output and the existing file are identical:
Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result. |
PRESORT optional |
Sorts the primary table on the primary key field before executing the command. Note You cannot use PRESORT inside the GROUP command. Omit PRESORT:
|
ISOLOCALE locale_code optional |
Note Applicable in the Unicode edition of Analytics only. The system locale in the format language_country. For example, to use Canadian French, enter fr_ca. Use the following codes:
If you do not use ISOLOCALE, the default system locale is used. |
Examples
Merge tables with identical key field names
The following example merges two tables with identical key field names:
OPEN Employees_Location_1 PRIMARY OPEN Employees_Location_2 SECONDARY MERGE ON Last_Name TO "AllEmployees" PRESORT
Merge tables with different key field names
The following example merges two tables with different key field names:
OPEN Employees_Location_1 PRIMARY OPEN Employees_Location_2 SECONDARY MERGE PKEY Last_Name SKEY Surname TO "AllEmployees" PRESORT
Remarks
Note
For more information about how this command works, see the Analytics Help.
Alternatives to merging
Merging can be tricky to perform correctly. You can get the same result by appending, or by extracting and appending, and then sorting.
For more information, see APPEND command, and EXTRACT command.
If the two source tables are already sorted, merging is more efficient and can execute more quickly.