upsert.meta

by

Summary

The [upsert].[meta] procedure is a lightweight controller for inserting or updating metadata attributes related to a specific object. It constructs a compact JSON payload and delegates the logic to [cdm].[patch_upsert].

It is ideal for managing metadata flags, status indicators, or classifications for objects like projects, employees, customers, and more.

🧭 Key Steps

  1. Set Defaults
    • Automatically fills in values for authoredby, sourceid, and create_date.
  2. Build JSON Payload
    • Constructs a small and structured payload using:
      • object_seq
      • meta_type
      • attribute
      • value
      • meta_data (as JSON string)
  3. Delegate to Patch Upsert
    • Passes everything to [cdm].[patch_upsert] for smart versioning and insert/update logic.
  4. Debug Output (Optional)
    • If @debug = 1, prints the payload and procedure reference for traceability.
  5. Error Handling
    • Raises a well-formed error message if anything fails during the operation.

🧪 Example Usage

EXEC [upsert].[meta]
    @tableName   = 'cdm.employee_values',
    @object_seq  = 'employee_1234',
    @meta_type   = 'values',
    @attribute   = 'language',
    @value       = 'EN',
    @meta_data   = '{"preferred": true}',
    @authoredby  = 'jkellett',
    @debug       = 1;

📦 Parameters

ParameterDescription
@tableNameThe metadata table where the record should be inserted/updated
@object_seqThe unique identifier for the object (e.g. employee_1234)
@meta_typeThe category or theme of the metadata (e.g. values, codes, dates)
@attributeThe name of the metadata field (e.g. language, status, region)
@valueThe value of the attribute (stored as string)
@meta_dataOptional metadata in JSON format (additional qualifiers or notes)
@tableTypeTemplate category (meta by default)
@authoredbySource name recorded in the insert/update
@sourceidOptional source system reference
@create_dateOptional creation timestamp
@debugEnable debug logging of payload and steps

🧩 JSON Payload Example

{
  "object_seq": "employee_1234",
  "meta_type": "values",
  "attribute": "language",
  "value": "EN",
  "meta_data": "{\"preferred\": true}"
}

✅ Best Practice

  • Use for single-attribute updates where object_seq, meta_type, and attribute form the natural key.
  • Ideal for small jobs like tagging objects with flags, lookup mappings, or enrichment values.
  • Fits neatly into automation pipelines or UI triggers.

Leave a Comment