---
id: database--database-writer
title: "Table writer"
summary: "Inserts, updates, upserts or deletes one row in a database table for every input record. The record type is generated from the table definition. After an insert, database-generated values (identity, primary key, computed columns) are written back into the record. Works inside a *Sql transaction* scope."
audience: user
keywords: "input, continue, connectionstring, typeprefix, tablename, writeoperation, commandtimeout, keeporiginalnames"
---
# Table writer

Inserts, updates, upserts or deletes one row in a database table for every input record. The record type is generated from the table definition. After an insert, database-generated values (identity, primary key, computed columns) are written back into the record. Works inside a *Sql transaction* scope.

## Pins

| Pin | Kind | Types | Description |
|---|---|---|---|
| Input | input | generated `<flow namespace>.<TypePrefix><Table>Table` | The row to write. |
| Continue | continue output | same as input (the table type) | Called with the (updated) input record after an insert or update. |

## Configuration

| Field | Type | Default | Description |
|---|---|---|---|
| `ConnectionString` | database link name | | Required. Name of a repository database link (link selector). Looked up by exact name; a raw connection string or `#name` prefix is not resolved. |
| `TypePrefix` | string | `Db` | Prefix of the generated type name. |
| `TableName` | string (database explorer) | | Required. `table` or `schema.table`; without a schema the link's default schema is used. Triggers reconfigure. |
| `WriteOperation` | dropdown: `1` Insert, `2` Update, `3` Upsert, `4` Delete | `1` | Operation per record. |
| `CommandTimeOut` | integer (seconds) | `20` | Command timeout. |
| `KeepOriginalNames` | boolean | `false` | `true` keeps column names as property names (and the type name unconverted); `false` converts them to C# names. |

## Behaviour

- Reconfigure (changing `TableName`) reads the table definition and generates a locked type named `TypePrefix` + table name + `Table` (suffix omitted if the name already ends with `Table`; converted to a C# name unless `KeepOriginalNames`). It becomes the only type of `Input` and `Continue`. A component with its default name is renamed to `TWD_<TableName>`. Changing `TypePrefix` or `KeepOriginalNames` alone does not regenerate the type.
- Every table column is bound from the input property with the same SQL column name; each statement sends only the columns it uses. Update and delete find the row by its primary key (and identity) columns in the `WHERE` clause; on ClickHouse by the sorting / primary / partition key.
- Operations:
  - `1` Insert: inserts the row; generated values (identity, primary key, computed columns) are read back in the same statement and written into the input record: SQL Server `OUTPUT` (through a temporary table when the table has enabled triggers), PostgreSQL `RETURNING`, Oracle `RETURNING ... INTO`, Db2 `FINAL TABLE`, MySQL/MariaDB a `SELECT` by `LAST_INSERT_ID()` in the same command (only with an auto increment column). Then `Continue`.
  - `2` Update: updates by primary key, then `Continue` (also when no row matched).
  - `3` Upsert: updates the row with the record's key, or inserts the record when there is none, in one atomic statement, and reads back generated values like insert. Then `Continue`. Per database:
    - SQL Server: `MERGE ... WITH (HOLDLOCK)`.
    - PostgreSQL: `INSERT ... ON CONFLICT (primary key) DO UPDATE`; when the key is an identity column, an update and an insert-if-not-updated in one statement (a new row gets a new id, so there is nothing to collide on).
    - MySQL/MariaDB: `INSERT ... ON DUPLICATE KEY UPDATE`. The auto increment column is inserted too: a record with an id that isn't in the table is inserted with that id (NULL or 0 generates one). It also fires on other unique indexes, updating the row that matches there.
    - Oracle: a PL/SQL block: update, insert when no row was updated, update again if a concurrent writer inserted the key first.
    - Snowflake: `MERGE` (no generated values read back).
    - Db2: update, then insert when no row was updated, in two statements. Not atomic: two records with the same new key at the same time can both insert, which fails on the primary key. ClickHouse doesn't support upsert.
  - `4` Delete: deletes by primary key. `Continue` is not called.

## Notes

- The input type is taken from the first `Input` connection at start, and it must contain a property for every table column; otherwise the flow fails to start. Use the generated type (for example via a *Data mapper*).
- Delete does not call `Continue`; nothing after a delete-mode writer runs.
- Update, upsert and delete need a primary key (or identity) column, and update/upsert at least one other writeable column; otherwise the flow fails to start.
- Snowflake and ClickHouse don't return generated values; the record keeps what it had.
- ClickHouse: update is a lightweight `UPDATE`, which needs the table setting `enable_block_number_column = 1` (`ALTER TABLE ... MODIFY SETTING enable_block_number_column = 1`); on other tables it fails. Arrays can't be NULL: a null array property is written as an empty array.
- One statement per record: for large volumes use *Bulk insert*.

## Example

```json
{ "ConnectionString": "MainDb", "TableName": "dbo.Customers", "WriteOperation": 3, "TypePrefix": "Db" }
```

Json deserializer → Data mapper → **Table writer** → (Continue) Http response