> ## Documentation Index
> Fetch the complete documentation index at: https://agents.nanonets.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server Upsert

> Insert-or-update tool for Microsoft SQL Server exposed through the Agents Platform.

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`](/docs/tools/mssql_execute_query#authentication): 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)`:

```sql theme={null}
MERGE INTO [schema].[table] WITH (HOLDLOCK) AS target
USING (SELECT @p1 AS [col_a], @p2 AS [conflict_col]) AS source
ON target.[conflict_col] = source.[conflict_col]
WHEN MATCHED THEN UPDATE SET target.[col_a] = source.[col_a]
WHEN NOT MATCHED THEN INSERT ([col_a], [conflict_col]) VALUES (source.[col_a], source.[conflict_col])
OUTPUT $action;
```

`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](/docs/tools/mssql_execute_query#authentication).