SQL Server : MERGE causing cannot insert the value NULL into column

Question

I am doing a MERGE in SQL Server using the following code

MERGE INTO DW_Datawarehouse.[dbo].[DWF_WAREHOUSE] with (HOLDLOCK) AS target
USING #DataSource AS Source 
      ON target.[DWF_WAREHOUSE_ORDER_NUM] = source.[DWF_WAREHOUSE_ORDER_NUM]
         AND target.DWF_WAREHOUSE_ORDER_TYPE_CD = source.DWF_WAREHOUSE_ORDER_TYPE_CD

WHEN MATCHED AND 
      (
      --all non-key fields, attributes and amounts
        source.[DWF_WAREHOUSE_ORDER_DESC] <> target.[DWF_WAREHOUSE_ORDER_DESC]
        or source.[DWF_WAREHOUSE_PICK_DATE_DAY] <> target.[DWF_WAREHOUSE_PICK_DATE_DAY]
        or source.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM] <> target.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM]
        or source.[DWF_WAREHOUSE_DIFF_QTY] <> target.[DWF_WAREHOUSE_DIFF_QTY]
        )
   THEN 
      UPDATE  
      SET target.[DWF_WAREHOUSE_ORDER_DESC] = source.[DWF_WAREHOUSE_ORDER_DESC],
          target.[DWF_WAREHOUSE_PICK_DATE_DAY] = source.[DWF_WAREHOUSE_PICK_DATE_DAY],
          target.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM] = source.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM],
          target.[DWF_WAREHOUSE_DIFF_QTY] = source.[DWF_WAREHOUSE_DIFF_QTY]

WHEN NOT MATCHED BY TARGET THEN
    INSERT ([DWF_WAREHOUSE_ORDER_DESC], [DWF_WAREHOUSE_PICK_DATE_DAY],
            [DWF_WAREHOUSE_DAY_OF_WEEK_NUM], [DWF_WAREHOUSE_DIFF_QTY])
    VALUES (source.[DWF_WAREHOUSE_ORDER_DESC], source.[DWF_WAREHOUSE_PICK_DATE_DAY],
            source.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM], source.[DWF_WAREHOUSE_DIFF_QTY])

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

I am getting the error:

Cannot insert the value NULL into column 'DWF_WAREHOUSE_ORDER_NUM', table 'DW_Datawarehouse.dbo.DWF_WAREHOUSE'; column does not allow nulls. UPDATE fails.

How would I modify my MERGE code to avoid this error?

Answer 1

It sound like there is null of DWF_WAREHOUSE_ORDER_NUM in data Source. you can change data source to

SELECT * FROM #DataSource AS WHERE DWF_WAREHOUSE_ORDER_NUM IS NOT NULL

Example:

MERGE INTO DW_Datawarehouse.[dbo].[DWF_WAREHOUSE] with (HOLDLOCK) as target
  USING
(SELECT * FROM #DataSource AS WHERE DWF_WAREHOUSE_ORDER_NUM IS NOT NULL)AS Source

  ON target.[DWF_WAREHOUSE_ORDER_NUM] = source.[DWF_WAREHOUSE_ORDER_NUM]
  AND target.DWF_WAREHOUSE_ORDER_TYPE_CD = source.DWF_WAREHOUSE_ORDER_TYPE_CD

WHEN MATCHED AND 
  (
  --all non-key fields, attributes and amounts
    source.[DWF_WAREHOUSE_ORDER_DESC] <> target.[DWF_WAREHOUSE_ORDER_DESC]
    or source.[DWF_WAREHOUSE_PICK_DATE_DAY] <> target.[DWF_WAREHOUSE_PICK_DATE_DAY]
    or source.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM] <> target.[DWF_WAREHOUSE_DAY_OF_WEEK_NUM]
    or source.[DWF_WAREHOUSE_DIFF_QTY] <> target.[DWF_WAREHOUSE_DIFF_QTY]
    )
.....