Server : MERGE导致无法将值NULL插入列中
Server : MERGE导致无法将值NULL插入列中
提问于 2016-12-21 03:51:41
我使用以下代码在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;我发现了一个错误：
无法将值NULL插入‘DWF_仓库_ORDER_NUM’列、表‘DW_Datawarehouse.dbo.DWF.dbo.DWF_仓库’；列不允许空值。更新失败。
如何修改我的MERGE代码以避免此错误？
回答 1
Stack Overflow用户
发布于 2016-12-21 04:41:48
听起来数据源中的DWF_WAREHOUSE_ORDER_NUM是空的。可以将数据源更改为
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]
)
.....页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41254814
复制相关文章
相似问题
腾讯云开发者
