Skip to main content
This document covers mssql_upsert, the insert-or-update tool for Microsoft SQL Server exposed through the Agents Platform.

​
Authentication

Same as mssql_execute_query: SQL Authentication credentials plus TLS options (encrypt, trust_server_certificate, host_name_in_certificate) and an optional schema (default dbo), all injected by the platform from the stored integration — not entered per run.

​
mssql_upsert

Inserts a row into a SQL Server table, or updates it in place when a conflict key already exists, via MERGE ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT. This is the SQL Server equivalent of postgres_upsert’s INSERT ... ON CONFLICT. This is a mutating tool.

​
Inputs

Required:
  • table: Target table name. The table must have a unique constraint on conflict_column — see the concurrency note below.
  • conflict_column: Unique/primary key column used to detect an existing row. Must be present in every row’s values.
  • One of:
    • values: a single column → value object (or a ${VAR_N} reference to one).
    • rows: an array of column → value objects for a batch upsert (or a ${VAR_N} reference to one) — the whole batch commits or rolls back together.
    • query: a complete SQL statement (INSERT/UPDATE/DELETE/MERGE, or a SELECT/OUTPUT-returning statement) run directly, bypassing the structured path entirely.
Optional:
  • updated_at_column: column set to SYSUTCDATETIME() when an existing row is updated.
Only one of values, rows, or query should be supplied; query takes priority and ignores the others.

​
MERGE semantics and the concurrency caveat

The generated statement wraps the target in WITH (HOLDLOCK):
HOLDLOCK narrows, but does not fully eliminate, SQL Server’s documented MERGE race under concurrent writers: two simultaneous MERGE statements against the same key can both evaluate WHEN NOT MATCHED and both attempt an insert, causing a duplicate-key error or a deadlock. Unlike Postgres’s ON CONFLICT (atomic at the engine level), this is a real, if narrow, residual risk. v1 does not retry on a duplicate-key error — this is a known limitation, not a bug. Mitigate it by:
  • Always having a unique constraint on conflict_column (required regardless — without it, MERGE’s WHEN NOT MATCHED semantics are meaningless).
  • Avoiding very high write concurrency against the same key from multiple agent tasks running in parallel.
Table/schema/column identifiers are validated (^[a-zA-Z_][a-zA-Z0-9_]*$) and then bracket-quoted ([identifier]) before being placed in the generated SQL — this guards against SQL Server’s long reserved/contextual keyword list (user, key, order, identity, transaction, …) colliding with a real column name. Values are always parameterized (@p1, @p2, ...), never interpolated.

​
Batch upsert transaction semantics

Unlike Postgres’s pgx.Batch (one network round-trip for the whole batch), the SQL Server driver has no equivalent pipelining API: each row in rows runs as its own MERGE statement, sequentially, inside one transaction — so a batch of N rows means N round-trips, not N separate all-or-nothing commits. Correctness is unchanged (the whole batch still commits or rolls back together); only latency scales with batch size.

​
Output

Single-row (values) mode:
  • inserted: true if newly inserted, false if an existing row was updated
  • rows_affected, table, conflict_column, conflict_value
Batch (rows) mode:
  • total, inserted, updated, unchanged counts, table, conflict_column
Raw query mode:
  • command: the statement’s leading keyword (INSERT, UPDATE, MERGE, …)
  • rows_affected: exact for a plain write with no OUTPUT clause (from the driver’s result); for a SELECT or an OUTPUT-returning statement, a best-effort count of returned rows
  • columns, rows, truncated when the statement returns rows

​
Notes

  • rows/values/query all accept a ${VAR_N} reference to a prior step’s output, so a large batch doesn’t need to be re-emitted as a literal argument.
  • Every upsert execution emits a HIPAA audit event (external_call, provider mssql) recording the host, latency, and outcome — SQL Server, like SFTP, is a raw (non-HTTP) protocol, so this is emitted manually rather than via the generic HTTP audit transport.
  • For the self-signed-certificate escape hatch (trust_server_certificate), see SQL Server Execute Query.