upsert

You are looking at an older version of the documentation. The latest version is found here.

This procedure retrieves data from a source and performs UPDATE (on existing entries) or INSERT (on new entries) operations on a target table.

Parameters

Parameter	Description
`dwh_table`	This parameter is obsolete. This is the target table, not necessarily in DWH. Use `target_table` for further development
`source_table`	Source object; mandatory
`keyColumnsArray`	Columns that identify new and existing rows. `keyColumns` will never be updated; mandatory
`updateColumns`	Set of columns that influence what will be updated
`invertUpdateColumns`	Parameter that decides whether all `updateColumns` will be updated (`FALSE`) or all except the `updateColumns` will be updated (`TRUE`); default: `TRUE`
`target_table`	Target table
`surrogateKeyType`	How to calculate the surrogate key. Can have one of the following values: `COUNTER` (column type `long`) `UUID` (column type `string`) `NULL` (no surrogate key is generated); default
`surrogateKeyName`	If `surrogateKeyType` is not null, an additional column with the surrogate key will be added to the target table. Type of column depends on `surrogateKeyType`
`dbmsTableCreationOptions`	Redshift table creation options
`checkMaxField`	Check field or expression in the `UPDATE` statement
`defaultvalueIfCheckMaxFieldIsNull`	Default value if check field or expression variable is null

If invertUpdateColumns is set to TRUE, key columns will be implicitly excluded from the columns to update. Nevertheless, you can never enumerate the same columns in keyColumnsArray and updateColumns.

Usage

SQL

CALL "UTILS.upsert"(
    "dwh_table" => 'string_dwh_table',
    "source_table" => 'string_source_table',
    "keyColumnsArray" => object_keyColumnsArray,
    "updateColumns" => object_updateColumns,
    "invertUpdateColumns" => boolean_invertUpdateColumns,
    "target_table" => 'string_target_table',
    "surrogateKeyType" => 'string_surrogateKeyType',
    "surrogateKeyName" => 'string_surrogateKeyName',
    "dbmsTableCreationOptions" => 'string_dbmsTableCreationOptions',
    "checkMaxField" => 'string_checkMaxField',
    "defaultvalueIfCheckMaxFieldIsNull" => 'string_defaultvalueIfCheckMaxFieldIsNull'

);;

Example

SQL

CALL "UTILS.upsert"
	( 
		source_table => '"mssql.AdventureWorks.HumanResources.Employee"',
		keyColumnsArray => array ('EmployeeID'),
		updateColumns => array ('EmployeeID'),
		invertUpdateColumns => TRUE,
		target_table => '"oracle.employee"',
		surrogateKeyType => 'UUID',
		surrogateKeyName => 'SurrogateUUID',
		dbmsTableCreationOptions => NULL,
		checkMaxField => 'ModifiedDate',
		defaultvalueIfCheckMaxFieldIsNull => '''2000-01-01'''
	);;

Parameters

Usage

Example

See Also