Introduction to 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. There are 4 main scenarios:
Source table is SQL statement, target table is new (the target either does not exist yet, or you've selected an existing table and checked
Drop and recreate the existing target table during the Choose a Target Destination step).
Source table is SQL statement, target table already exists, and
Drop and recreate the existing target table is NOT checked during the Choose a Target Destination step.
Source is a single table, target table is new (the target either does not exist yet, or you've selected an existing table and checked
Drop and recreate the existing target table during the Choose a Target Destination step).
Source is a single table, target table already exists and
Drop and recreate the existing target table is NOT checked during the Choose a Target Destination step.
Below are specififc notes for every scenario:
The Source is a SQL statement, the Target table is new
There is one entry for each column in the source SQL statement and all columns must be mapped to be valid.
The source columns are not editable. If you want to change a source column, you must do it in the source SQL statement.
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.
The Source is a SQL statement, the Target table exists
There is one entry for each column in the source SQL statement and all columns must be mapped to be valid.
The source columns are not editable. If you want to change a source column, you must do it in the source SQL statement.
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, you must select
Drop and recreate the existing target table in step 2.
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.
The Source is a table, the Target table is 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 to duplicate 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.
The Source is a table, the 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 to duplicate 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 mapped to source columns with the same name by default. 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, you must select
Drop and recreate the existing target table in step 2.
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.