AUTO_MERGE procedure¶
Automatically inserts/updates (“upserts”) data from SOURCE_TABLE into DEST_TABLE, based on DEST_KEY.
Prototypes¶
AUTO_MERGE(SOURCE_SCHEMA VARCHAR(128), SOURCE_TABLE VARCHAR(128), DEST_SCHEMA VARCHAR(128), DEST_TABLE VARCHAR(128), DEST_KEY VARCHAR(128))
AUTO_MERGE(SOURCE_SCHEMA VARCHAR(128), SOURCE_TABLE VARCHAR(128), DEST_SCHEMA VARCHAR(128), DEST_TABLE VARCHAR(128))
AUTO_MERGE(SOURCE_TABLE VARCHAR(128), DEST_TABLE VARCHAR(128), DEST_KEY VARCHAR(128))
AUTO_MERGE(SOURCE_TABLE VARCHAR(128), DEST_TABLE VARCHAR(128))
Description¶
The AUTO_MERGE procedure performs an “upsert”, or combined insert and update of all data from SOURCE_TABLE into DEST_TABLE by means of an automatically generated MERGE statement.
The DEST_KEY parameter specifies the name of the unique key to use for identifying rows in the destination table. If specified, it must be the name of a unique key or primary key which covers columns which exist in both the source and destination tables. If omitted, it defaults to the name of the primary key of the destination table.
If SOURCE_SCHEMA and DEST_SCHEMA are not specified they default to the current schema.
Only columns common to both the destination table and the source table will be included in the generated statement. Destination columns must be updateable (they cannot be defined as GENERATED ALWAYS), and the executing user must have INSERT and UPDATE privileges on the destination table.
Parameters¶
- SOURCE_SCHEMA
- If provided, specifies the schema containing SOURCE_TABLE. If omitted, defaults to the value of the CURRENT SCHEMA special register.
- SOURCE_TABLE
- Specifies the name of the table within SOURCE_SCHEMA from which data will be read.
- DEST_SCHEMA
- If provided, specifies the schema containing DEST_TABLE. If omitted, defaults to the value of the CURRENT SCHEMA special register.
- DEST_TABLE
- Specifies the name of the table within DEST_SCHEMA into which data will be inserted or updated. This table must have at least one unique key (or a primary key).
- DEST_KEY
- If provided, specifies the name of the unique key in the destination table which will be joined to the equivalently named fields in the source table to determine whether rows are to be inserted or updated. If omitted, defaults to the name of the primary key of the destination table.
Examples¶
Merge new content from EMP_SOURCE into the EMPLOYEES table, matching rows via the primary key of EMPLOYEES, then delete rows in EMPLOYEES that no longer exist in EMP_SOURCE:
CALL AUTO_MERGE('EMP_SOURCE', 'EMPLOYEES');
CALL AUTO_DELETE('EMP_SOURCE', 'EMPLOYEES');
Merge new content from STAGING.CONTRACTS into IW.CONTRACTS, using a specific unique key for matching rows:
CALL AUTO_MERGE('STAGING', 'CONTRACTS', 'IW', 'CONTRACTS', 'CONTRACTS_KEY');