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.
INSERT INTO target_table (col1, col2, col3)
SELECT a, b, c ...
In this case, the mapping of the fields
a, b, c
to the column names col1, col2, col3
are 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 ...
Last modified 1yr ago