ACL Scripting Guide 14.1

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.

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>
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.

  • ON key_fields the key field or fields to merge on if the corresponding key fields in the primary and secondary tables have the same name

    If the corresponding fields have different names, or if they are expressions rather than actual physical fields, you must use PKEY and SKEY.

  • PKEY primary_key_fields the key field or fields, or expression, in the primary table
  • SKEY primary_key_fields the key field or fields, or expression, in the secondary table

Sorting requirement

The 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 sorting

The 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:

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

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:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

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:

  • the same fields
  • the same field order
  • matching fields are the same length
  • matching fields are the same data type

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:

  • If the primary key field is already sorted
  • If you are merging two tables using an indexed common key field

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:

  • language ISO 639 standard language code
  • country ISO 3166 standard country code

    If you do not specify a country code, the default country for the language is used.

If you do not use ISOLOCALE, the default system locale is used.

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

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.