Output and computed columns

Choose which columns a target receives, add computed columns with worked examples, and see how SQL Flow types them on the target.

By default a target receives every source column. Choose output columns replaces that with a list you control: you keep some source columns, add computed ones, and every column you don't list is dropped.

Before you begin

• Know the table's primary key, unique keys and foreign keys. Those columns must stay in the list unchanged.
• See Validation and limits for the source settings that rules need.

Choose the output columns of one table

1. Open Configure for the target and select the table.
2. Tick Choose output columns.
3. For each source column to keep, type its name in Source column to keep and select Keep source column. The column is added with its quoted name as the expression, such as "id".
4. To add a computed column, select Add computed column, then enter an Output name and a Source expression.
5. Select Remove beside any column you don't want.
6. Select Done in the wizard, or Save and reload… on an existing pipeline.

List every column to keep, including all source key columns. A source column that isn't listed isn't created at the target.

Expressions always read the original source values, never another output column. Only the operators and functions in the Expression reference are accepted: CONVERT, TRY_CAST, ISNULL, LEN, SUBSTRING and YEAR, for example, are refused before the start. See Expression errors for every code.

Examples

The examples are written in T-SQL for a SQL Server source and use a table dbo.Orders with these columns:

Each example gives the Output name and the Source expression to enter. Keep order_id in the list as well, because it is the key.

Join text

CONCAT joins exactly two text values, so nest it to join more. The result is NULL when either value is NULL; wrap it in COALESCE to fall back to another value. Cast a number to text before joining it.

In T-SQL, + adds numbers only: first_name + ' ' + last_name is refused with numeric-type. On a source whose dialect has the || operator, such as Oracle, first_name || ' ' || last_name works like the nested CONCAT.

Clean up text

TRIM removes leading and trailing spaces only, not tabs or line breaks.

Calculate amounts

+, - and * compute exact numbers and never round:

line_total is created as decimal(38, 2). To choose the type, cast the result, for example CAST(quantity * unit_price AS DECIMAL(18,2)).

A cast never rounds, so a value with too many decimal places stops the pipeline. CAST(unit_price * 1.2 AS DECIMAL(18,2)) works for 10.00 (12.000) and stops with numeric-overflow-or-rounding for 9.99 (11.988). There is no division and no rounding function: unit_price / 2 is refused with unsupported-operator.

Label rows with CASE

A searched CASE tests conditions in order and returns the first match. A NULL condition counts as false, so a NULL quantity gets unit here:

A simple CASE compares one value with each WHEN:

Without ELSE, a row that matches no WHEN gets NULL, and the column is nullable.

Replace missing values

COALESCE returns its first value that isn't NULL:

COALESCE(discount, 0) alone becomes decimal(38, 2); the cast keeps the source's decimal(10,2). Use COALESCE, not ISNULL or NVL, which are refused.

Derive flags

A comparison, an IN list or a NULL check produces a Boolean column, created as bit:

A flag is NULL when a value it compares is NULL, except for IS NULL and IS NOT NULL.

Change a type

Text converts only from exact forms: '1,200.00' stops CAST(amount_text AS DECIMAL(12,2)) with invalid-number, and '2024-05-03' stops a cast to DATETIME2 with datetime-cast. See Casts for every conversion.

Work with dates and times

There are no date functions: YEAR(ordered_at) is refused, and so is CAST(ordered_at AS DATE). You can keep a date column, cast it to text or to another date and time type of the same kind, test it for NULL, and compare it with another value of the same kind:

Add a constant column

A constant column has the same value in every row, for example to tag rows with their origin:

A NULL needs a cast to give the column a type: a bare NULL is refused with invalid-output-facets.

Columns that must pass through unchanged

These columns must be in the list as a plain column reference, with the output name equal to the source column name:

• every primary key column;
• every column of a unique index;
• every column of a foreign key, on either side of the relationship.

SQL Flow writes and deletes target rows by key, so these values can't be computed, masked or renamed by the rule. To give such a column another name at the target, use Column names in the same dialog. A missing or changed key column is refused with protected-column-missing-or-transformed, and a renamed one with protected-column-alias.

For the key order_id:

A computed column can still read a key, as order_ref does above, as long as the key itself is also in the list unchanged.

Output columns for all tables

Under All tables, open Output columns and masks for all tables (optional) and tick Choose global output columns. The list is a template: it applies to each table that has every column its expressions name. Other tables keep all their columns. A table that chooses its own output columns ignores the template and the global masks.

For example, when every table you want to trim has the key id and the columns email and updated_at, this template keeps only those three columns in each of them, with email in lowercase:

The template is a complete list, so it drops every other column of the tables it applies to. A table with these columns whose key isn't id is refused with protected-column-missing-or-transformed; give that table its own list. To change a few columns and keep the rest, use global masks instead.

Names at the target

The Output name names the column in the rule. The table's Column names then rename it at the target, the same way they rename a source column. Output names must be unique within a table, ignoring case, after the renames are applied (output-name-collision). For example, the outputs Email and email collide.

How computed columns are typed

Prepare target schema creates the target table with the output columns. A kept source column keeps its source type and is mapped to the target like any other column. A computed column takes the type of its expression:

For example, full_name above (CONCAT of two nvarchar(50) columns and ' ') is nvarchar(101), and price_with_tax is decimal(38, 3). A computed text column that can be longer than 4,000 characters (nvarchar) or 8,000 characters (varchar) becomes nvarchar(max). On SQL Server targets, computed text columns use the binary collation Latin1_General_100_BIN2.

A computed column is nullable when any of its inputs is nullable. COALESCE is nullable only when all its arguments are.

Large values

A large-object column, such as nvarchar(max) or varbinary(max), can be kept as a plain column reference whatever its size. Inside an expression:

• Values up to 1 MiB work with every operation.
• Larger values work with HASH_MASK, PARTIAL_MASK, CONCAT, LOWER, UPPER, TRIM, COALESCE, CASE, comparisons, IS NULL, AND, OR and NOT. Any other operation on such a value stops the pipeline with processing_lob_stream_operation_unsupported.

The same large-object column can't be listed twice (processing_lob_duplicate_output_unsupported), and it can't be a key (processing_lob_key_unsupported). See Large values and LOBs.

A computed text column that becomes nvarchar(max) needs a large-object column among its inputs. For example, CONCAT of two nvarchar(4000) columns can reach 8,000 characters without one, so it is refused with processing-computed-output-requires-lob.

Computed columns on the source

An expression can read a computed source column when the column is numeric (not money or smallmoney) and its definition uses only numeric arithmetic, CAST, CONVERT with no style or style 0, and COALESCE. Other computed columns are refused when a rule reads them, for example with processing_derived_source_unsupported or generated-function-unproved.

See also

• Masking
• Expression reference
• Table and column names
• Validation and limits