MERGE command

Concept Information

Merge tables

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|ON ALL <EXCLUDE field_name <...n>>}|{PKEY primary_key_fields|PKEY ALL <EXCLUDE field_name <...n>>} {SKEY secondary_key_fields|SKEY ALL <EXCLUDE field_name <...n>>}} <IF test> TO table_name <LOCAL> <OPEN> <WHILE test> <FIRST range|NEXT range> <APPEND> <PRESORT> <ISOLOCALE locale_code>

Note

Only character fields, or character computed fields, can be used as key fields in MERGE.

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.

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.

Parameters

Name Description
ON key_fields | ON ALL

Note

You can only use ON if the corresponding key fields in the primary and the 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.

The key field or fields in both the primary and the secondary tables.

  • ON key_fields use the specified field or fields

    Fields are used in the order that you list them.

  • ON ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.
EXCLUDE field_name

optional

Only valid when merging using ON ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune ON ALL, by excluding the specified fields.

EXCLUDE must immediately follow ON ALL. For example:

ON ALL EXCLUDE field_1 field_2
PKEY primary_key_fields | PKEY ALL

The key field or fields, or expression, in the primary table.

  • PKEY primary_key_fields use the specified field or fields

    Fields are used in the order that you list them.

  • PKEY ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.
EXCLUDE field_name

optional

Only valid when merging using PKEY ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune PKEY ALL, by excluding the specified fields.

EXCLUDE must immediately follow PKEY ALL. For example:

PKEY ALL EXCLUDE field_1 field_2
SKEY secondary_key_fields | SKEY ALL

The key field or fields, or expression, in the secondary table.

  • SKEY secondary_key_fields use the specified field or fields

    Fields are used in the order that you list them.

  • SKEY ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.
EXCLUDE field_name

optional

Only valid when merging using SKEY ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune SKEY ALL, by excluding the specified fields.

EXCLUDE must immediately follow SKEY ALL. For example:

SKEY ALL EXCLUDE field_1 field_2

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.

The LOCAL parameter must immediately follow the TO parameter.

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.

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

For more information about how this command works, see Merging tables.

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.