Merging Data Into an Existing Data Source With ON TABLE MERGE

The ON TABLE MERGE clause in a TABLE or TABLEF command enables you to insert, update, and delete records in an existing single-segment data source.

Data sources that support insert, update, and delete operations, either for individual records or bulk load, can take advantage of the full set of options for ON TABLE MERGE. Relational data sources support all of these options. Other data sources, such as delimited and fixed format sequential data sources, can only have records appended to the existing data source.

ON TABLE MERGE Processing

An ON TABLE MERGE request consists of two sections.

A TABLE or TABLEF request that retrieves the data from the source file to be merged into the target file.
All TABLE syntax is supported, from any type of data source that has an accompanying synonym. The request can include joins, DEFINEs, COMPUTEs, and any other supported syntax. The records retrieved are stored in an internally-generated HOLD file that is used as the input to the MERGE phase of the request.
All TABLE syntax is supported, from any type of data source that has an accompanying synonym. The request can include joins, DEFINEs, COMPUTEs, and any other supported syntax. The records retrieved are either passed directly to a Relational DBMS in an SQL request (when the request can be optimized), loaded using the SQL MERGE command (when the request cannot be optimized but the database supports the SQL MERGE command), or stored in an internally-generated HOLD file that is used as the input to a MODIFY request.

The target must be a single-segment file with a corresponding synonym, and it must have a Write Adapter that enables WebFOCUS to update it.

Supported Merge Phrases

MATCHING expression. Specifies the expression that selects the source and target records to be passed to the UPDATE, DELETE, or INSERT commands. If records are only to be appended, no MATCHING phrase is used.
WHEN MATCHED [AND expression] UPDATE. Updates matching records. Optionally, provides an additional expression that can be used to perform different updates to different sets of target records. To perform multiple sets of updates, include multiple WHEN MATCHED UPDATE phrases.
The new target field values are calculated using expressions specified following the WHEN MATCHED UPDATE phrase.
WHEN MATCHED [AND expression] DELETE. Deletes matching records. Optionally, provides an additional expression that can be used to delete multiple sets of target records. To delete multiple sets of records, include multiple WHEN MATCHED DELETE phrases.
WHEN NOT MATCHED INSERT. Is used to insert new records when no field values satisfy the MATCHING expression.
The new target field values are calculated using expressions specified following the WHEN NOT MATCHED INSERT phrase.
INSERT. When there is no MATCHING phrase and no WHEN NOT MATCHED phrase, INSERT is used to append new records.
The new target field values are calculated using expressions specified following the INSERT phrase.

Properties of the Generated MERGE Request

The merge request can be generated in several ways.

Optimized request. If the source and target are in the same database (have the same suffix and use the same connection), and MERGE (or INSERT from SELECT, for INSERT) is supported by the DBMS, the whole request passed to the DBMS in an SQL request.

With an optimized request, the MATCHING expression can be any expression, and if any record fails in the merge request, the entire merge is rejected.
Non-Optimized request. If for any reason, the request cannot be optimized, for example if the target and source are from different databases or use different connections, or, if the TABLE request contains syntax that cannot be optimized, one of the following methods is used to do the merge.
- If the target database supports bulk load into a temporary table, and also supports MERGE, then the data is loaded using bulk load into the target database as a temporary table, and the target table is updated using the SQL MERGE command with the temporary table as input.
- If the target database supports MERGE but not bulk load, then the data will be loaded on a record-by-record basis, one request per record. This is more efficient than using a MODIFY request (which happens if the target database does not support MERGE), as MODIFY uses two requests per record (SELECT+INSERT or SELECT+UPDATE).
- If the target database does not support MERGE, a HOLD file of the values retrieved by the TABLE request is generated in DATREC (fast binary) format.
  With a DATREC HOLD file, the processing is done by a simple MODIFY request. The MATCHING expression must match on key fields, and processing is done on a record-by-record basis, so if a record fails the merge request, the request continues with the next record.

No logs are generated for an ON TABLE MERGE request. Therefore, when working with production files, creating a backup of the source file prior to running the request is recommended. At the end of the merge processing, messages are generated that indicate if the merge was successful and how many records were affected. The following image shows an example of the messages displayed for an optimized request.

Merge Request properties

The following image shows an example of the messages displayed for a non-optimized request.

displayed messages

If not all records were processed and you want to know which records were rejected, you must either enable traces or look in the Session Log.

How to Merge Data Into an Existing Data Source With ON TABLE MERGE

In the following syntax, at least one UPDATE, DELETE, or INSERT command must be supplied. Multiple UPDATE and DELETE commands are supported when multiple expressions provide separate update or delete conditions and target field values.

{TABLEF|TABLE} FILE
...
ON TABLE MERGE INTO FILE
target_mf
[MATCHING matching_expression;]

[
  WHEN MATCHED [AND matching_expression2;] UPDATE
   target_field1=expression1;
   target_field2=expression2; ...
   ...
]

[
  WHEN MATCHED [AND matching_expression3;] DELETE
  ...
]

[
  [WHEN NOT MATCHED] INSERT
   target_field3=expression3;
   target_field4=expression4; ...
]

END

where:

target_mf: Is the name of the Master File for the target data source that is to be updated.
MATCHING matching_expression: Is the expression to match in order to apply the INSERT, UPDATE, and DELETE commands. The expression can be complex. For a non-optimized request, where the merge is performed by a MODIFY procedure, all key fields must be matched in the expression. Omit this phrase when using the INSERT command to append records. If the field names are the same in the source and target files, use the prefix SRC. to reference fields in the source file, and the prefix TRG. to reference fields in the target file.
matching_expression2: Is an additional expression to match in order to apply the UPDATE command. If the field names are the same in the source and target files, use the prefix SRC. to reference fields in the source file, and the prefix TRG. to reference fields in the target file.
target_field1, target_field2 ...: Are one or more fields to be updated in the target data source.
expression1, expression2: Are expressions used to calculate the target field values to be used for updating the existing target field values. These expressions can use fields from the TABLE request and the target data source. If the field names are the same in the source and target files, use the prefix SRC. to reference fields in the source file, and the prefix TRG. to reference fields in the target file.
matching_expression3: Is an additional expression to match in order to apply the DELETE command. If the field names are the same in the source and target files, use the prefix SRC. to reference fields in the source file, and the prefix TRG. to reference fields in the target file.
target_field3, target_field4 ...: Are the target field values for the new record to be inserted when the value of matching_expression is not found in the target data source.
expression3, expression4, expression5: Are expressions used to calculate the target field values to be used for inserting the new record. These expressions can use fields from the TABLE request. Use the prefix SRC. to reference these fields.

Example: Updating and Inserting Records Using ON TABLE MERGE

This example uses source and target data sources from the DataMigrator - General tutorial. To access the tutorial, click the New button (+) on the Applications page ribbon and click Tutorials, or right-click an application folder, point to New, and click Tutorials. Scroll down to the DataMigrator - General tutorial. Select or enter values for the DBMS, Connection, prefix, and application, and click Create.

The following request updates records in the table dmrpts from records in the table dminv, when the field value PROD_NUM in dminv (SRC.PROD_NUM) matches the field value PROD_NUM in dmrpts (TRG.PROD_NUM). When the PROD_NUM fields do not match, records are inserted.

TABLE FILE dminv
PRINT
   PROD_NUM
COMPUTE QUANTITY = SUM.QTY_IN_STOCK;
COMPUTE LINEPRICE = SUM.PRICE;
COMPUTE LINECOST = SUM.COST;
COMPUTE LINECOGS = LINECOST * QUANTITY;

ON TABLE MERGE INTO FILE dmrpts
MATCHING TRG.PROD_NUM EQ SRC.PROD_NUM;

WHEN MATCHED UPDATE
  QUANTITY=SRC.QUANTITY;
  YRMTH = TRG.YRMTH+1;
  LINEPRICE=SRC.LINEPRICE;
  LINECOGS=SRC.LINECOGS;
  PROFIT=SRC.LINECOGS - SRC.LINECOST;

WHEN NOT MATCHED INSERT
  STORE_CODE='1004NY';
  PROD_NUM=SRC.PROD_NUM;
  YRMTH=202101;
  QUANTITY=SRC.QUANTITY;
  LINEPRICE=SRC.LINEPRICE;
  LINECOGS=SRC.LINECOGS;
  PROFIT=SRC.LINECOGS - SRC.LINECOST;
END

The following TABLE request prints the values in the updated dmrpts target.

TABLE FILE DMRPTS
PRINT *
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The following image of the last page of the output shows the updated values in the existing records, and the inserted records (the last two rows).

Existing and inserted records

Example: Appending a Record With ON TABLE MERGE

This example uses GGSALES as the source and a DATREC version of GGPRODS, named GGDATREC, as the target. The following image shows GGDATREC in its original state.

Appending ON TABLE MERGE

The following request appends one record to the GGDATREC target using ON TABLE MERGE. There is no MATCHING or WHEN NOT MATCHED phrase.

TABLE FILE ggsales
SUM
  FST.PCD
  FST.PRODUCT
  FST.UNITS
  FST.DOLLARS

ON TABLE MERGE INTO FILE GGDATREC
INSERT
  PRODUCT_ID=SRC.PCD;
  PRODUCT_DESCRIPTION=SRC.PRODUCT;
  VENDOR_CODE='V400';
  VENDOR_NAME='Acme Foods';
  PACKAGE_TYPE='Case';
  SIZE = 18;
  UNIT_PRICE = SRC.DOLLARS/SRC.UNITS;
END

The following TABLE request prints the values in the updated ggdatrec target.

TABLE FILE GGDATREC
PRINT *
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The following image shows the appended record (the last row).

appended record

Example: Deleting Records Using ON TABLE MERGE

The following request uses the dminv table and a single-segment version of the dmord table generated by the DataMigrator - General tutorial.

The following request displays all of the plant locations in the dmordsgl data source.

TABLE FILE DMORDSGL
SUM QUANTITY
BY PLANT
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image.

output

The following ON TABLE MERGE request deletes records where the source product number matches the target product number and the plant is 'LA'.

TABLE FILE dminv
PRINT
PROD_NUM
ON TABLE MERGE INTO FILE dmordsgl
MATCHING TRG.PROD_NUM EQ SRC.PROD_NUM;
WHEN MATCHED AND PLANT EQ 'LA'; DELETE
END

Running this request and then the request to display the plants shows that the records with the LA plant have been deleted, as shown in the following image.

LA Plant Records

Best Practices for ON TABLE MERGE

We strongly recommend that you make a backup copy of your target data source prior to using ON TABLE MERGE, in case you need to restore it to its original contents.
Always use the SRC. and TRG. prefixes in the merge expressions, for safety and clarity.
The TABLE commands prior to the ON TABLE MERGE clause internally generate a HOLD file, and the merge is done from the HOLD file into the target file. The HOLD file generates alias names for the source fields in the form E01, E02, and so on. These names are unique to each field, while field names in the TABLE request may generate duplicate field names in the HOLD file. Unique names must be used, so either use the alias names, or assign a new name to a duplicate field name using the AS phrase. For example, the following request generates three fields named DOLLARS:
```
TABLE FILE GGSALES
SUM DOLLARS MAX.DOLLARS MIN.DOLLARS
BY UNITS
ON TABLE HOLD
END
```
The generated Master File shows the duplicate field names and unique alias names.
```
FILENAME=HOLD, SUFFIX=FIX , IOTYPE=BINARY, $
  SEGMENT=HOLD, SEGTYPE=S1, $
    FIELDNAME=UNITS, ALIAS=E01, USAGE=I08, ACTUAL=I04, $
    FIELDNAME=DOLLARS, ALIAS=E02, USAGE=I08, ACTUAL=I04, $
    FIELDNAME=DOLLARS, ALIAS=E03, USAGE=I08, ACTUAL=I04, $
    FIELDNAME=DOLLARS, ALIAS=E04, USAGE=I08, ACTUAL=I04, $
```
The following version of the request generates unique field names using the AS phrase.
```
TABLE FILE GGSALES
SUM DOLLARS AS TOTDOLL
MAX.DOLLARS AS MAXDOLL
MIN.DOLLARS AS MINDOLL
BY UNITS
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS UNIQNAME
END
```
This request generates the following Master File.
```
FILENAME=UNIQNAME, SUFFIX=FIX , IOTYPE=BINARY, $
  SEGMENT=UNIQNAME, SEGTYPE=S1, $
    FIELDNAME=UNITS, ALIAS=E01, USAGE=I08, ACTUAL=I04, $
    FIELDNAME=TOTDOLL, ALIAS=E02, USAGE=I08, ACTUAL=I04, $
    FIELDNAME=MAXDOLL, ALIAS=E03, USAGE=I08, ACTUAL=I04, $
    FIELDNAME=MINDOLL, ALIAS=E04, USAGE=I08, ACTUAL=I04, $
```
Using this Master File, you can use either field names or alias names. Also note that in an ON TABLE MERGE request, the ON TABLE SET ASNAMES ON command is not needed in order for the AS names to be propagated to the HOLD Master File.

When an ON TABLE MERGE request is optimized, an SQL request is generated and passed to the DBMS. For example, the following request is optimized when the dminv and dmrpts tables use the same adapter and connection:

TABLE FILE dminv
PRINT
 PROD_NUM
 COMPUTE QUANTITY = SUM.QTY_IN_STOCK;
 COMPUTE LINEPRICE = SUM.PRICE;
 COMPUTE LINECOST = SUM.COST;
ON TABLE MERGE INTO FILE dmrpts
MATCHING TRG.PROD_NUM EQ SRC.PROD_NUM;
 WHEN MATCHED UPDATE
  YRMTH=TRG.YRMTH + 1;
  QUANTITY=SRC.QUANTITY;
  LINEPRICE=SRC.LINEPRICE;
  LINECOGS=SRC.LINECOGS;
  PROFIT=SRC.LINECOGS - SRC.LINECOST;

WHEN NOT MATCHED INSERT
  STORE_CODE='1004NY';
  PROD_NUM=SRC.PROD_NUM;
  YRMTH=202101;
  QUANTITY=SRC.QUANTITY;
  LINEPRICE=SRC.LINEPRICE;
  LINECOGS=SRC.LINECOGS;
  PROFIT=SRC.LINECOGS - SRC.LINECOST;
END

You can open the Session Log from the User Options menu on the Web Console title bar. The Session Log shows that the following SQL request was generated.

SELECT
  T1."PROD_NUM" AS "E01",
  T1."QTY_IN_STOCK" AS "E02",
  T1."PRICE" AS "E03",
  T1."COST" AS "E04",
  (T1."COST" * T1."QTY_IN_STOCK") AS "E05",
  '1004NY' AS "E06",
  202101 AS "E07",
  T1."QTY_IN_STOCK" AS "E08",
  ((T1."COST" * T1."QTY_IN_STOCK") - T1."COST") AS "E09"
  FROM
  dminv T1;

MERGE INTO dmrpts AS T3
  USING ( SELECT
  T1."PROD_NUM" AS "E01",
  T1."QTY_IN_STOCK" AS "E02",
  T1."PRICE" AS "E03",
  T1."COST" AS "E04",
  (T1."COST" * T1."QTY_IN_STOCK") AS "E05",
  '1004NY' AS "E06",
  202101 AS "E07",
  T1."QTY_IN_STOCK" AS "E08",
  ((T1."COST" * T1."QTY_IN_STOCK") - T1."COST") AS "E09"
  FROM
  dminv T1) T2
  ON
  (T3."PROD_NUM" =
  T2."E01")

WHEN MATCHED THEN UPDATE SET
  "YRMTH" = ("YRMTH" + 1),
  "QUANTITY" =
  T2."E08",
  "LINEPRICE" =
  T2."E03",
  "LINECOGS" =
  T2."E05",
  "PROFIT" =
  T2."E09"

WHEN NOT MATCHED THEN INSERT (
  "PROD_NUM",
  "STORE_CODE",
  "YRMTH",
  "QUANTITY",
  "LINEPRICE",
  "LINECOGS",
  "PROFIT")
  VALUES (
  T2."E01",
  T2."E06",
  T2."E07",
  T2."E08",
  T2."E03",
  T2."E05",
  T2."E09")

The following messages indicate that the request was optimized.

     (FOC2659) FULL OPTIMIZATION OF MERGE WITH SUBSELECT HAS BEEN DONE
     (FOC2661) TARGET FILE ibisamp/dmrpts

     (FOC2665) MERGE PROCESS STARTED AT 15.33.19
     (FOC1796) ROWS AFFECTED BY MERGE STATEMENT: 182

Example: Using Alias Names With ON TABLE MERGE

The following request uses alias names from the source HOLD file in the expressions used to insert fields into the target file.

TABLE FILE dminv
PRINT
  PROD_NUM
  COMPUTE YM = 202102;
  COMPUTE QUANTITY = QTY_IN_STOCK;
  COMPUTE LINEPRICE = PRICE;
  COMPUTE LINECOGS = LINEPRICE * QUANTITY;
ON TABLE MERGE INTO FILE ibisamp/dmrpts
 INSERT
  STORE_CODE='AAAA';
  PROD_NUM=SRC.E01;
  YRMTH=SRC.E02;
  QUANTITY=SRC.E03;
  LINEPRICE=SRC.E04;
  LINECOGS=SRC.E05;
  PROFIT=SRC.E04 - SRC.E05;
END

Release: 8207
Category: Creating Content
Product: Reporting Language