SQL Server:合并导致无法将值null插入列中

Question

我使用以下代码在SQL Server中进行MERGE

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;

我遇到了错误：

无法将值插入列中'dwf_warehouse_order_num'，表'dw_datawarehouse.dbo.dwf_warehouse';列不允许空。更新失败。

我将如何修改MERGE代码以避免此错误？

Answer 1

听起来好像数据源中有dwf_warehouse_order_num的null。您可以将数据源更改为

SELECT * FROM #DataSource AS WHERE DWF_WAREHOUSE_ORDER_NUM IS NOT NULL

示例：

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]
    )
.....