cdm.patch_upsert

Summary

The [cdm].[patch_upsert] procedure acts as a controller for metadata inserts and updates. It dynamically evaluates JSON payloads, identifies whether an existing active row already exists, and determines whether to call an insert (post_insert) or update (put_update) operation. This ensures consistent versioned metadata with minimal redundancy.

It supports JSON-driven payloads with field mapping based on the cont.table_templates definition and provides built-in logic for checksum comparison and metadata provenance.

---

🧭 Key Steps

1. Initialise Defaults
   • Sets procedure name, current date, and fallback defaults for authoredby and sourceid.
2. Table Name Resolution
   • Parses @tableName into [schema].[table] format for fully qualified access.
3. Determine Table Type
   • Looks up metadata in [log].[table_list] to resolve tableType (e.g. meta, item, value).
4. Extract Parameters from JSON Payload
   • Uses metadata template to declare and extract values from the JSON payload.
5. Construct PK Match Expression
   • Dynamically builds a conditional string to detect existing active records (excluding EXPIRY_DATE).
6. Conditional UPSERT Logic
   • Executes either:
     • cdm.post_insert if no matching active record exists, or
     • cdm.put_update if a match exists and a data change is detected (via checksum).
7. Debug Logging
   • Outputs key logic and SQL strings when @debug = 1.
8. Error Handling
   • Captures full context in case of failure, including procedure name, user, and detailed message.

---

🧪 Example Usage

EXEC [cdm].[patch_upsert]
    @tableName   = 'cdm.project_codes',
    @payload     = N'{
                      "OBJECT_SEQ": "project_123-456",
                      "META_TYPE": "codes",
                      "ATTRIBUTE": "category",
                      "VALUE": "internal",
                      "META_DATA": "{}"
                   }',
    @authoredby  = 'jkellett',
    @debug       = 1;

---

📦 Parameters

Parameter	Description
@tableName	Full or partial name of target metadata table (schema inferred if missing)
@payload	JSON payload containing metadata fields
@debug	Enables debug output of SQL and logic flow
@authoredby	Metadata source (default: this procedure)
@sourceid	Source system reference
@create_date	Optional override of creation timestamp
@tableType	Used to identify correct template for metadata extraction

---

📐 Under the Hood

• Uses cont.table_templates to drive field mapping, key detection, and value typing
• Maintains metadata quality by validating existence and change before writing
• Operates with INSERT and UPDATE as separate delegate procedures, supporting clearer version control

---

✅ Good to Know

• Requires post_insert and put_update procedures to be implemented consistently
• Designed for metadata tables only – not general-purpose fact/data tables
• Highly reusable for domain-specific views like employee_codes, project_dates, or customer_values