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.

 

1. 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.
     
2. 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.
     
3. 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.
     
4. 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.