i must merge multible database tables in a node js windows service. so i decided to write a method that creates a sql string for this. here is my method: this method and the created sql string works nice, expect in one case. if the value in the source table column is NULL the target will not be updated. here

Merge Null Values in Merge Command

i must merge multible database tables in a node js windows service.
so i decided to write a method that creates a sql string for this.

here is my method:

function createSQLString(targetTable, sourceTable, columns){
let sql = "MERGE " + targetTable + " AS TARGET USING " + sourceTable + " AS SOURCE n";

switch (sourceTable) {
    //.....some other cases.....
    case 'communications':
        sql += "ON (" +
            " TARGET.[id] = SOURCE.[id]" +
            " AND TARGET.[business_partner_id] = SOURCE.[business_partner_id]" +
            " AND TARGET.[fiscalYear] = SOURCE.[fiscalYear]" +
            ") n";
        break;
    default:
        // other stuff
}

// if row was found and values are not equal
sql += "WHEN MATCHED AND (";
    columns.forEach(column => {
        sql += "SOURCE.[" + column.COLUMN_NAME + "] <> TARGET.[" + column.COLUMN_NAME + "] OR ";
    })
    sql = sql.substring(0, sql.length -4) + "n";
sql += ") THEN UPDATE SET ";
    columns.forEach(column => {
        sql += "TARGET.[" + column.COLUMN_NAME + "] = SOURCE.[" + column.COLUMN_NAME + "],"
    })
    sql = sql.substring(0, sql.length -1) + "n";

// if no match in target insert
sql += "WHEN NOT MATCHED BY TARGET THEN INSERT (";
    columns.forEach(column => {
        sql += "[" + column.COLUMN_NAME + "], ";
    })
    sql = sql.substring(0, sql.length -2) + "n";
sql += ") VALUES ("
    columns.forEach(column => {
        sql += "SOURCE.[" + column.COLUMN_NAME + "], ";
    })
    sql = sql.substring(0, sql.length -2) + "n";
sql += ")n"

// if there is a record in target but not in source
sql += "WHEN NOT MATCHED BY SOURCE n THEN DELETE;"
return sql;
}

this method and the created sql string works nice, expect in one case.
if the value in the source table column is NULL the target will not be updated.
here is a screenshot for that case (table 1 target, table 2 source):

why isnt it be updating and how to solve it?

edit: here is generated sql string:

MERGE communication AS TARGET USING communication_cache AS SOURCE 
ON (
TARGET.[id] = SOURCE.[id] AND 
TARGET.[fiscalYear] = SOURCE.[fiscalYear] AND 
TARGET.[clientId] = SOURCE.[clientId]
) 
WHEN MATCHED AND (
SOURCE.[id] <> TARGET.[id] OR 
SOURCE.[address_type] <> TARGET.[address_type] OR 
SOURCE.[is_correspondence_address] <> TARGET.[is_correspondence_address] OR 
SOURCE.[is_main_post_office_box_address] <> TARGET. 
[is_main_post_office_box_address] OR 
SOURCE.[is_main_street_address] <> TARGET.[is_main_street_address] OR 
SOURCE.[is_management_address] <> TARGET.[is_management_address] OR 
SOURCE.[business_partner_id] <> TARGET.[business_partner_id] OR 
SOURCE.[fiscalYear] <> TARGET.[fiscalYear]
) 
THEN UPDATE SET 
TARGET.[id] = SOURCE.[id],
TARGET.[address_type] = SOURCE.[address_type],
TARGET.[is_correspondence_address] = SOURCE.[is_correspondence_address],
TARGET.[is_main_post_office_box_address] = SOURCE. 
[is_main_post_office_box_address],
TARGET.[is_main_street_address] = SOURCE.[is_main_street_address],
TARGET.[is_management_address] = SOURCE.[is_management_address],
TARGET.[business_partner_id] = SOURCE.[business_partner_id],
TARGET.[fiscalYear] = SOURCE.[fiscalYear]
WHEN NOT MATCHED BY TARGET THEN INSERT (
[id], [address_type], [is_correspondence_address], 
[is_main_post_office_box_address], [is_main_street_address], 
[is_management_address], [business_partner_id], [fiscalYear]
) VALUES (SOURCE.[id], SOURCE.[address_type], SOURCE. 
[is_correspondence_address], SOURCE.[is_main_post_office_box_address], 
SOURCE.[is_main_street_address], SOURCE.[is_management_address], SOURCE. 
[business_partner_id], SOURCE.[fiscalYear]
)
WHEN NOT MATCHED BY SOURCE 
THEN DELETE

Answer

NULL is a special case in any RDBMS.

When you write below condition in WHEN MATCHED clause:

SOURCE.[is_correspondence_address] <> TARGET.[is_correspondence_address] OR

it is not considering NULL for comparison – any comparison with NULL results into NULL.

If you want to handle NULL in comparison, you can write below clause (assumming is_correspondence_address is VARCHAR/NVARCHAR datatype):

    ISNULL(SOURCE.[is_correspondence_address],'NOTDEFINED') 
      <> ISNULL(TARGET.[is_correspondence_address], 'NOTDEFINED') OR

Advertisement

Answer