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.