'SSIS merge join only when left table has null columns

I want to achieve this merge statement using SSIS. I know this can be achieved using a merge join in SSIS but I am not sure if I can fit COALESCE statement anywhere. I wanted merge to happen between two tables only when the target table has NULL records.

MERGE TargetTable AS TARGET
USING SourceTable AS SOURCE

ON  ISNULL(TARGET.ColA, '')      = ISNULL(SOURCE.ColA, '')
    AND ISNULL(TARGET.ColB, '')  = ISNULL(SOURCE.ColB, '')

WHEN MATCHED THEN UPDATE SET
TARGET.ColC = COALESCE(TARGET.ColC, SOURCE.ColC)
TARGET.ColD = COALESCE(TARGET.ColD, SOURCE.ColD)
TARGET.ColE = COALESCE(TARGET.ColE, SOURCE.ColE)

WHEN NOT MATCHED [BY TARGET] THEN INSERT
(ColA, ColB, ColC, ColD, ColE)

Values(SOURCE.ColA, SOURCE.ColB, SOURCE.ColC, SOURCE.ColD, SOURCE.ColE)
sql-serverjoinmergessis


Solution 1:[1]

As mentioned in the comments, you can simply use derived columns transformations to achieve the ISNULL() and COALESCE() functions.

There are two approaches:

  1. Using REPLACENULL() function:
REPLACENULL(ColA,"")
  1. Using the conditional operator ? : with the ISNULL() function:
ISNULL([ColA]) == True ? "" : [ColA]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 zoher.festo2

Related Questions