MAP_COLUMNS_BY_NAME

Maps columns from the SELECT statement to the table by the names of the returned columns in the query.

If set, all columns in the query must either have an alias, be a simple column reference, or be a star expression.

Note that this is required when using a star query in the SELECT statement.

Examples

A basic INSERT statement may look as follows:

INSERT INTO target_table (col1, col2, col3)
    SELECT a, b, c ...

In this case, mapping the fields a, b, c to the column names col1, col2, col3 is based on their ordinal positions.

However, since Upsolver supports tables with dynamic columns, you may be unable to provide a full list of columns when mapping to a dynamic list of columns. As such, there would be no way to map columns based on ordinality.

In such a case, the MAP_COLUMNS_BY_NAME keyword should be used with the resulting SQL looking as follows:

INSERT INTO target_table MAP_COLUMNS_BY_NAME
    SELECT a AS col1, 
           b AS col2, 
           c AS col3 ...

When mapping a column directly as seen below with col3:

INSERT INTO target_table MAP_COLUMNS_BY_NAME
    SELECT a AS col1, 
           b AS col2, 
           col3 ...

Upsolver assumes the intended column name to be col3 and maps it accordingly.

In the case that the column name cannot be inferred, the job will fail to run.

For example, if you have a calculated field:

INSERT INTO target_table MAP_COLUMNS_BY_NAME
    SELECT col + 'a' ...

Typically, SQL would name col + 'a' as col1 by default; in SQLake, however, this is not allowed.

To ensure columns are mapped correctly when using MAP_COLUMNS_BY_NAME, a column name must be provided in the query if it cannot be directly inferred as shown below:

INSERT INTO target_table MAP_COLUMNS_BY_NAME
    SELECT col + 'a' AS col1 ...

This also applies when you have a calculated field from using a function:

INSERT INTO target_table MAP_COLUMNS_BY_NAME
    SELECT ABS(col) AS col1 ...