Cont.upsert_bulk_entity

by

The SQL stored procedure [cont].[upsert_bulk_entity] is designed to handle the bulk insertion or updating (upserting) of entity data from a source table into a target entity table. Here’s a summary of the key steps involved:

  1. Procedure Parameters:
    • The procedure takes several parameters, including the source table name, entity details (name, code, class), schema names, and optional debugging flag. These parameters guide the procedure’s actions and allow customization.
  2. Initialization and Setup:
    • Various parameters and variables are initialized, including the current procedure name, source type, and target table name. The procedure ensures that necessary defaults are set if certain parameters are not provided.
  3. Source Table Processing:
    • The procedure determines the type of the source table and forms the fully qualified name of the table. It also sets up source identifiers and processes the entity description and label if they are not provided.
  4. Target Table Creation:
    • If the target table does not exist, it will be created using the cdm.create_table_entity procedure. This ensures that the data has a valid destination for upsertion.
  5. Temporary Table Creation:
    • A temporary table [temp].[upsert_bulk_entity] is created to store intermediate data. This includes entity codes, classes, descriptions, and labels, along with a row number for iteration.
  6. Data Processing Loop:
    • The procedure loops through the rows in the temporary table, extracting data for each entity. For each row, it calls the cdm.upsert_entity procedure to upsert the data into the target entity table.
  7. Error Handling:
    • A TRY...CATCH block is implemented to handle errors. If an error occurs, a detailed error message is raised, including the procedure name, source table, and the specific error encountered.
  8. Debugging:
    • If debugging is enabled (@debug = '1'), various SQL commands and steps are printed to the console for troubleshooting.

Overall, this procedure is designed to efficiently manage the bulk upsertion of entity data from a specified source to a target table, with robust error handling and the ability to debug the process if needed.

Leave a Comment