Add Custom Columns to Target/Data Warehouse Tables

How to add a custom column / field to the target table loaded into the data warehouse.

Preparation

• Decide on the name and data type of the custom column/field you want to add to the target table.

• Design the formula/calculation for the value of the custom column. Code the formula as a T-SQL expression.

Add the Custom Column with the AS Keyword

1. Add the custom column to the SELECT clause in the Extract SQL Statement panel.
   Use the AS syntax. For example 1 as [my_count]

2. Open the Design page .

3. On the Design page select the row for the custom column.

   1. Modify the Dw Data Type as required

   2. Enter the T-SQL expression into T-SQL Expression.

   3. Confirm.

4. Make note of the load option selected in the Load panel.

5. Select Load All in the Load panel.
   Select Load Now .

6. Restore the load setting as required – see step #4.

The SQL used in the Extract SQL Statement must be compatible with the Source Object.
For more on this see ETL+ Extract SQL Statement Panel. ETL+ Extract SQL Statement Panel | SourceDrivers has links for the right SQL Syntax to Use with Various Source Drivers and Source Systems.

Using the AS keyword in the SELECT statement

The ASkeyword adds a column to the result-set of the SELECT query. The syntax does not allow you to assign a specific data type to the column.

The data type of the new column is determined by SQL based on the the value or expression in the AS expression.

• 1️⃣ The data type shown in the Source Data Type column on the ETL+ Design Page.

• ETL+ Extract SQL Statement Panel | SourceDrivers has links for the right SQL Syntax to Use with Various Source Drivers and Source Systems.

AS SYNTAX

• <constant> AS <new-column-name>

  • <constant> == a number (e.g.; 22, 99.1) or string ("brown fox")

• <expression> AS <new-column-name>

  • <expression> == date, datetime, string or numeric SQL function

  • <expression> == A constant, function, any combination of column names, constants, and functions connected by an operator or operators, or a subquery.

Miscellaneous

Tricks and Workarounds

• CAST(<existing-column> AS DATETIME) AS new_hire_date

Related Pages

• ETL+ Extract SQL Statement: CONVERT() – T-SQL’s CONVERT function vs. ANSI standard SQL CAST function. is platform agnostic.

• ETL+ Source Drivers