Cont.upsert_bulk_meta_fields

The procedure [cont].[upsert_bulk_meta_fields] is a SQL Server stored procedure designed for bulk upsert operations on metadata fields from source tables into a target metadata table. The procedure accepts various parameters to define the source and target tables, as well as the metadata structure, and supports batch processing.

Key Steps in the Procedure:

1. Initialization: Sets up various parameters and retrieves the source table type.
2. Source Table Identification: Uses the sourceSchema and sourceName to determine the table type from INFORMATION_SCHEMA.TABLES.
3. Default Values and Setup:
   • Sets default values for parameters if they are NULL.
   • Prepares SQL statements and variables for processing.
4. Dynamic SQL for Table Creation:
   • Generates dynamic SQL to create a target table if it doesn’t exist.
   • Constructs the SQL for processing entity and metadata fields dynamically based on the inputs.
5. Field Processing Loop:
   • Loops through the columns in the source table or a specified list of measures.
   • For each field, it creates a temporary table with metadata information ([temp].[upsert_bulk_meta]).
6. Batch Processing:
   • Processes rows in batches (default size 1000) to manage large data volumes efficiently.
   • For each batch, metadata is calculated and upserted into the target table using another stored procedure cdm.upsert_meta.
7. Error Handling:
   • The procedure includes comprehensive error handling using TRY...CATCH blocks, raising detailed error messages if something goes wrong.

Purpose:

The procedure is primarily used to insert or update metadata fields in bulk from a source table to a target table, handling large datasets efficiently through batching. It ensures that the target table is created if it doesn’t exist, processes fields dynamically, and handles potential errors gracefully.