如何避免Type2维度表因空值插入新行并正确更新Type1列

需求规则 #

• 维护包含Type1列的Type2维度表[dim].[patient]，需满足：
  • 若stage最新数据的StateID_number为空白/NULL，且目标表当前行该字段已有有效值，则不执行更新
  • 仅当stage与目标表的有效值存在差异时，才更新Type1字段
  • 禁止因stage中的空值/空白被误判为新值，导致目标表插入额外行（目标表已有该ID的当前行时）

当前问题 #

现有代码会错误插入StateID_number为NULL的重复行，违反需求。

原代码 #

CREATE TABLE [dim].[patient](
    [dim_patient_current_de_key] [int] IDENTITY(1,1) NOT NULL,
    [id] [varchar](20) NULL,
    [client] [varchar](40) NULL,
    [DOB] [datetime] NULL,
    [Gender_Identity_code] [varchar](50) NULL,--type 2
    [dss_load_date] [datetime] NULL,
    [dss_start_date] [datetime] NULL,
    [dss_end_date] [datetime] NULL,
    [dss_current_flag] [char](1) NULL,
    [dss_version] [int] NULL,
    [dss_create_time] [datetime] NULL,
    [dss_update_time] [datetime] NULL,
    [StateID_number] [varchar](240) NULL,
    CONSTRAINT [dim_patient_demographi_idx_0] PRIMARY KEY CLUSTERED 
    ([dim_patient_current_de_key] ASC)
)
-------- Type1字段更新逻辑
UPDATE [dim].[dim_patient] WITH ( TABLOCK )
SET     client =  changes.client_name
      , DOB =  changes.DOB
      , SSN =  changes.SSN
      , StateID_number = CASE 
              WHEN NULLIF(changes.StateID_number, '') IS NULL THEN [TABLEOWNER].[dim_patient].StateID_number
              ELSE changes.StateID_number
              END
      , dss_update_time = @v_current_datetime
FROM 
(
    SELECT stage_patient.id  id
         , stage_patient.client  client
         , stage_patient.DOB  DOB
         , stage_patient.Gender_Identity_code
         , stage_patient.StateID_number  StateID_number
    FROM [stage].[stage_patient] stage_patient
    EXCEPT
    SELECT dim_patient.id  id
         , dim_patient.client  client
         , dim_patient.DOB  DOB
         , dim_patient.Gender_Identity_code
         , dim_patient.StateID_number  StateID_number
    FROM [dim].[dim_patient]
    WHERE dim_patient.dss_current_flag = 'Y'
) AS changes
WHERE dim_patient.id = changes.id
AND   dim_patient.dss_current_flag = 'Y'
----------------------------------------------------------------
--============================================================================
-- 插入新记录逻辑
--============================================================================
INSERT INTO [dim].[patient] WITH ( TABLOCK )
( id
, client
, DOB
, Gender_Identity_code
, StateID_number
, dss_load_date
, dss_start_date
, dss_end_date
, dss_current_flag
, dss_version
, dss_create_time
, dss_update_time
)
SELECT DISTINCT 
  stage_patient.id
, stage_patient.client
, stage_patient.DOB
, stage_patient.Gender_Identity_code
, stage_patient.StateID_number
, stage_patient.dss_load_date
, CASE WHEN vers.patid IS NULL
THEN CAST('01-JAN-1900' AS datetime)
ELSE @v_current_date
END
, CAST('31-DEC-2999' AS datetime)
, 'Y'
, CASE WHEN vers.patid IS NULL
THEN 1
ELSE vers.dss_version + 1
END
, @v_current_datetime
, @v_current_datetime
FROM [stage].[stage_patient] stage_patient
LEFT OUTER JOIN (
SELECT patid
, MAX(dss_version) dss_version
FROM [stage].[patient] patient
GROUP BY id
) AS vers
ON    stage_patient.id = vers.id
EXCEPT
SELECT patient.id  id
, patient.client  client
, patient.DOB  DOB
, patient.StateID_number  StateID_number
, source.dss_load_date  dss_load_date
, @v_current_date
, CAST('31-DEC-2999' AS datetime)
, 'Y'
, dss_version + 1
, @v_current_datetime
, @v_current_datetime
FROM [dim].[patient] patient
JOIN
(
SELECT stage_patient.id AS id
, stage_patient.dss_load_date AS dss_load_date
FROM [stage].[stage_patient] stage_patient
) AS source 
ON    patient.id = source.id
WHERE patient.dss_current_flag = 'Y'

修正后的代码及说明 #

核心修改点

1. 统一空值处理：在所有比较逻辑中，将StateID_number的空白转为NULL，避免空白与NULL被视为不同值
2. 修正笔误：原代码中client_name应为client，TABLEOWNER为冗余前缀，子查询表名[stage].[patient]应为[dim].[patient]
3. 调整差异判断逻辑：确保只有当有效值不同时才触发更新/插入

CREATE TABLE [dim].[patient](
    [dim_patient_current_de_key] [int] IDENTITY(1,1) NOT NULL,
    [id] [varchar](20) NULL,
    [client] [varchar](40) NULL,
    [DOB] [datetime] NULL,
    [Gender_Identity_code] [varchar](50) NULL,--type 2
    [dss_load_date] [datetime] NULL,
    [dss_start_date] [datetime] NULL,
    [dss_end_date] [datetime] NULL,
    [dss_current_flag] [char](1) NULL,
    [dss_version] [int] NULL,
    [dss_create_time] [datetime] NULL,
    [dss_update_time] [datetime] NULL,
    [StateID_number] [varchar](240) NULL,
    CONSTRAINT [dim_patient_demographi_idx_0] PRIMARY KEY CLUSTERED 
    ([dim_patient_current_de_key] ASC)
)
-------- Type1字段更新逻辑（修正后）
UPDATE [dim].[patient] WITH ( TABLOCK )
SET     client = changes.client
      , DOB = changes.DOB
      , SSN = changes.SSN
      , StateID_number = CASE 
              WHEN NULLIF(changes.StateID_number, '') IS NULL THEN [dim].[patient].StateID_number
              ELSE changes.StateID_number
              END
      , dss_update_time = @v_current_datetime
FROM 
(
    SELECT stage_patient.id
         , stage_patient.client
         , stage_patient.DOB
         , stage_patient.Gender_Identity_code
         , NULLIF(stage_patient.StateID_number, '') AS StateID_number
    FROM [stage].[stage_patient] stage_patient
    EXCEPT
    SELECT dim_patient.id
         , dim_patient.client
         , dim_patient.DOB
         , dim_patient.Gender_Identity_code
         , NULLIF(dim_patient.StateID_number, '') AS StateID_number
    FROM [dim].[patient]
    WHERE dim_patient.dss_current_flag = 'Y'
) AS changes
WHERE [dim].[patient].id = changes.id
AND   [dim].[patient].dss_current_flag = 'Y'
----------------------------------------------------------------
--============================================================================
-- 插入新记录逻辑（修正后）
--============================================================================
INSERT INTO [dim].[patient] WITH ( TABLOCK )
( id
, client
, DOB
, Gender_Identity_code
, StateID_number
, dss_load_date
, dss_start_date
, dss_end_date
, dss_current_flag
, dss_version
, dss_create_time
, dss_update_time
)
SELECT DISTINCT 
  stage_patient.id
, stage_patient.client
, stage_patient.DOB
, stage_patient.Gender_Identity_code
, stage_patient.StateID_number
, stage_patient.dss_load_date
, CASE WHEN vers.id IS NULL THEN CAST('01-JAN-1900' AS datetime) ELSE @v_current_date END
, CAST('31-DEC-2999' AS datetime)
, 'Y'
, CASE WHEN vers.id IS NULL THEN 1 ELSE vers.dss_version + 1 END
, @v_current_datetime
, @v_current_datetime
FROM [stage].[stage_patient] stage_patient
LEFT OUTER JOIN (
    SELECT id, MAX(dss_version) dss_version
    FROM [dim].[patient]
    GROUP BY id
) AS vers ON stage_patient.id = vers.id
WHERE NOT EXISTS (
    SELECT 1
    FROM [dim].[patient] patient
    WHERE patient.id = stage_patient.id
      AND patient.dss_current_flag = 'Y'
      AND patient.client = stage_patient.client
      AND patient.DOB = stage_patient.DOB
      AND patient.Gender_Identity_code = stage_patient.Gender_Identity_code
      AND NULLIF(patient.StateID_number, '') = NULLIF(stage_patient.StateID_number, '')
)

补充说明 #

• UPDATE逻辑中，通过NULLIF(StateID_number, '')统一空白与NULL的比较规则，避免无意义的更新触发
• INSERT逻辑改用NOT EXISTS替代原EXCEPT逻辑，更精准地判断当前行是否存在完全匹配的有效记录，彻底避免空值导致的重复插入

内容的提问来源于stack exchange，提问作者Drdre01