このプロシージャーは、Sources からデータを取得し、UPDATE (既存のエントリに対して) またはINSERT (新しいエントリに対して) オペレーションをターゲット テーブルに対して実行します。

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

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

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'''
);;

See Also

Using the Upsert Stored Procedure to Reproduce the SQL MERGE ソーステーブルからターゲットテーブルへのデータへの複数の挿入および更新を実行する方法については、こちらをご覧ください。