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

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

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