Column Mapping
Column mapping involves the proper mapping of Source to Target Columns.
This screen has many functions to allow changing of data before
it is written to the target table. The functionality changes depending
on what type of source and target has been selected previously. A
chart below outlines these differences.
The functionality on the column map changes depending on the source
and target selections made previously. The functionality can be
described in 4 scenarios.
Scenario 1) Source table is SQL statement, target table is new (does
not exist yet or if an Existing target table is selected with Drop
and recreate the existing target table checked on the Target step.)
Scenario 2) Source table is SQL statement, target table already exists
and Drop and recreate the existing target
table is not checked on the Target step.
Scenario 3) Source is a single table, target table is new (does not
exist yet or if an Existing target table is selected with Drop
and recreate the existing target table checked on the Target step.)
Scenario 4) Source is a single table, target table already exists and
Drop and recreate the existing target
table is not checked on the Target step.
- Source SQL statement, target table new
- There is one entry for each column in the source SQL statement
and all must be mapped to be valid.
- The source columns are not editable, to change a source column,
it must be done in the source SQL statement itself.
- The Row up and Row down buttons are visible, they are used
to control the order of columns in the new target table.
- The target column names are defaulted to be the same as the
source column names, however they can be changed along with their
attributes (data type, length, precision, scale)
- The Add column and Delete column buttons are not visible because
they do not apply to a source SQL statement, all source columns
must be mapped.
- The "Populate Columns" with "Clear Target"
and "Clear Source" buttons are not visible because they
do not apply to a new target table, they are for changing the
view of a mapping to an existing target table.
- Source SQL statement, target table exists
- There is one entry for each column in the source SQL statement
and all must be mapped to be valid.
- The source columns are not editable, to change a source column,
it must be done in the source SQL statement itself.
- The "Populate Columns - Source Columns" with "Clear
Target" buttons are visible and enabled. They can be
used to clear the default mappings and map the target columns
to the source columns.
- The "Populate Columns - Target Columns" with "Clear
Source" buttons are not enabled. They do not apply
when using a source SQL statement, all source columns must be
mapped.
- The target column names are by default mapped to source columns
with the same name. The target columns can be changed to
specify other target columns. However the target names cannot
be changed to a name that doesn't exist in the target table. To
change an existing target table, "Drop and recreate the existing
target table“ must be selected in step 2 Target.
- The Add column and Delete column buttons are not visible because
they do not apply to a source SQL statement, all source columns
must be mapped.
- The Row up and Row down buttons are not visible, they do not
apply to an existing table since the order of the table cannot
be changed.
- Source table, target table new
- The source columns can be changed to other columns in the table.
- The source column field itself can be edited to create a SQL
expression.
- The Add column and Delete column buttons are visible and can
be used to add more mappings using new sql expressions or duplicating
a source column to many target columns.
- The Row up and Row down buttons are visible, they are used
to control the order of columns in the new target table.
- The target column names are defaulted to be the same as the
source column names, however they can be changed along with their
attributes (data type, length, precision, scale)
- The "Populate Columns" with "Clear Target"
and "Clear Source" buttons are not visible because they
do not apply to a new target table, they are for changing the
view of a mapping to an existing target table.
- Source table, target table exists
- The source columns can be changed to other columns in the table.
- The source column field itself can be edited to create a SQL
expression.
- The Add column and Delete column buttons are visible and can
be used to add more mappings using new sql expressions or duplicating
a source column to many target columns.
- The "Populate Columns" with "Clear Target"
and "Clear Source" buttons are visible and enabled.
They can be used to clear the default mappings and map the
target columns to the source columns or map the source columns
to the target columns. This is helpful when the source and
target table have many rows that are different and will not be
mapped.
- The target column names are by default mapped to source columns
with the same name. The target columns can be changed to
specify other target columns. However the target names cannot
be changed to a name that doesn't exist in the target table. To
change an existing target table, "Drop and recreate the existing
target table“ must be selected in step 2 Target.
- The Row up and Row down buttons are not visible, they do not
apply to an existing table since the order of the table cannot
be changed.