SQL Server output

Load rows into a SQL Server table. Turn on All or nothing and a load that fails leaves the table as it was.

Category: Output
Connection: SQL Server

What it does

Loads the incoming rows into a Microsoft SQL Server table through a saved Connection. Appends, truncates-then-loads, or upserts (MERGE on the key columns). The table is auto-created from the incoming columns if it doesn't exist.

Inputs and outputs

Inputs	One.
Outputs	None. This node ends a pipeline.

When to use it

Write pipeline output into a SQL Server table
Refresh a table each run (truncate)
Idempotently merge by a key (upsert)

Examples

Load staging, then merge

Pre-SQL: TRUNCATE TABLE stg_orders · Post-SQL: MERGE orders AS t USING stg_orders AS s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.total = s.total WHEN NOT MATCHED THEN INSERT (id, total) VALUES (s.id, s.total);

A real MERGE, for what the write modes cannot express: soft deletes, slowly changing history, or a match that is not simple key equality.

Append

Table: dbo.Sales, Mode: append

Replace all rows

Mode: truncate

Empties the table first, then loads.

Upsert

Mode: upsert, Key columns: id

MERGE: insert new, update existing by id.

Tips

Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
A Test run (Run to here) executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
Credentials live on the Connection, never in the pipeline.
Auto-created text key columns become NVARCHAR(450) so they fit SQL Server's unique index.
Dates, times and decimals load natively, as DATE / DATETIME2(7) / DATETIMEOFFSET(7) / TIME(7) / DECIMAL(p,s), never the legacy DATETIME, which rounds to 1/300th of a second.
All or nothing (off by default): on, the create, the truncate and every insert or merge run in ONE transaction, so a failure leaves the table exactly as it was; off, each statement commits as it runs and a failure leaves the rows written so far.
On has costs on a busy server: readers of the table wait until the load commits (unless the database uses snapshot isolation), and the transaction log holds the whole load until it does, so a huge load can fill it. Pair Off with Upsert so a re-run overwrites rather than duplicates.