SQL Server Performance

tSQL MERGE STATEMENT - MANAGING A TYPE 2 SLOWLY CHANGING DIMENSION

Discussion in 'SQL Server 2008 General DBA Questions' started by jthuma68, Apr 27, 2009.

  1. jthuma68 New Member

    Overview of what I am trying to accomplish: But not with this schema but with my own:Handle the Type 2 ChangesNow we’ll do a second MERGE statement to handle the Type 2 changes.
    This is where things get a little tricky because there are several steps involved in tracking Type 2
    changes. Our code will need to:
    1. Insert brand new customer rows with the appropriate effective and end dates
    2. Expire the old rows for those rows that have a Type 2 attribute change by setting the
    appropriate end date and current_row flag = ‘n’
    3. Insert the changed Type 2 rows with the appropriate effective and end dates and current_row
    flag = ‘y’
    The problem with this is it’s one too many steps for the MERGE syntax to handle. Fortunately, the
    MERGE can stream its output to a subsequent process. We’ll use this to do the final insert of the
    changed Type 2 rows by INSERTing into the Customer_Master table using a SELECT from the
    MERGE results. This sounds like a convoluted way around the problem, but it has the advantage of
    only needing to find the Type 2 changed rows once, and then using them multiple times.
    The code starts with the outer INSERT and SELECT clause to handle the changed row inserts at the
    end of the MERGE statement. This has to come first because the MERGE is nested inside the
    INSERT. The code includes several references to getdate; the code presumes the change was
    effective yesterday (getdate()-1) which means the prior version would be expired the day before
    SOURCE DATA TABLE DEFINITION:CREATE
    TABLE [dbo].[STAGE_carrier_master]([COMMON_CARRIER_CODE] [char]
    (9) NULL,[ROW_INSERT_TS] [varchar]
    (25) NULL,[ROW_UPDATE_TS] [varchar]
    (25) NULL,[ROW_INSERT_USER_ID] [varchar]
    (30) NULL,[ROW_UPDATE_USER_ID] [varchar]
    (30) NULL,[REPLI_TOOL_INSERT_TS] [varchar]
    (25) NULL,[REPLI_TOOL_UPDATE_TS] [varchar]
    (25) NULL,[CARRIER_GROUP] [char]
    (5) NULL,[CARRIER_TYPE] [char]
    (3) NULL,[CARRIER_NAME] [varchar]
    (30) NULL,[CARRIER_ADDRESS1] [varchar]
    (30) NULL,[CARRIER_ADDRESS2] [varchar]
    (30) NULL,[CARRIER_CITY] [varchar]
    (25) NULL,[CARRIER_STATE] [char]
    (2) NULL,[CARRIER_ZIP] [varchar]
    (5) NULL,[CARRIER_ZIP_4] [varchar]
    (4) NULL,[CARRIER_PHONE] [varchar]
    (10) NULL,[CARRIER_FAX] [varchar]
    (10) NULL,[CARRIER_LOCATION_CODE] [varchar]
    (18) NULL,[CARRIER_NO] [int]
    NULL,[CARRIER_BILLING_ADDRESS1] [varchar]
    (30) NULL,[CARRIER_BILLING_ADDRESS2] [varchar]
    (30) NULL,[CARRIER_BILLING_CITY] [varchar]
    (25) NULL,[CARRIER_BILLING_STATE] [char]
    (2) NULL,[CARRIER_BILLING_ZIP] [char]
    (5) NULL,[CARRIER_BILLING_ZIP_4] [char]
    (4) NULL,[CARRIER_ACTIVE_INDICATOR] [char](3) NULL
    )
    ON [PRIMARY] DIMENSION TARGET TABLE
    CREATE
    TABLE [dbo].[DIMENSION_Carrier_SCD]([sk_Carrier_ID] [bigint]
    IDENTITY(1,1) NOT NULL,[pk_Carrier_Key] [bigint]
    NULL,[Common_Carrier_Code] [char]
    (9) NOT NULL,[Carrier_NBR] [int]
    NULL,[Tmse_Carrier_Code] [varchar]
    (20) NULL,[Carrier_Group] [char]
    (5) NOT NULL,[Carrier_Type] [char]
    (3) NULL,[Carrier_Name] [varchar]
    (30) NULL,[Carrier_Phone] [varchar]
    (10) NULL,[Carrier_Fax] [varchar]
    (10) NULL,[Carrier_Location_Code] [varchar]
    (18) NULL,[Carrier_Active_Indicator] [char]
    (3) NULL,[sk_Carrier_Physical_Address_ID] [bigint]
    NULL,[sk_Carrier_Billing_Address_ID] [bigint]
    NULL,[sk_Carrier_Other_Address_ID] [bigint]
    NULL,[Begin_Date] [datetime]
    NULL,[End_Date] [datetime]
    NULL,[Load_Date] [datetime]
    NULL,[ROW_UPDATE_TS] [varchar]
    (25) NULL,
    CONSTRAINT [PK__DIMENSIO__33A7782E689D8392] PRIMARY KEY CLUSTERED ([sk_Carrier_ID]
    ASC
    MY MERGE STATEMENT:INSERT
    INTO DIMENSION_Carrier_SCD(Common_Carrier_Code
    , --1Carrier_NBR
    , --2Carrier_Group
    , --3Carrier_Type
    , --4Carrier_Name
    , --5Carrier_Phone
    , --6Carrier_Fax
    , --7Carrier_Location_Code
    , --8Carrier_Active_Indicator
    , --9Begin_Date
    , --10End_Date
    , --11Load_Date
    --12)SELECTCommon_Carrier_Code
    , --1Carrier_NO
    , --2Carrier_Group
    , --3Carrier_Type
    , --4Carrier_Name
    , --5Carrier_Phone
    , --6Carrier_Fax
    , --7Carrier_Location_Code
    , --8Carrier_Active_Indicator
    , --9GETDATE
    ()-1, --10'12/31/2199'
    , --11GETDATE
    () --12FROM
    (MERGE DIMENSION_Carrier_SCD DCS USING STAGE_Carrier_Master SCM
    ON
    (DCS
    .Common_Carrier_Code = SCM.Common_Carrier_Code)
    WHENNOT MATCHED THEN
    INSERT VALUES
    (SCM
    .COMMON_CARRIER_CODE, --1SCM
    .CARRIER_NO, --2SCM
    .CARRIER_GROUP, --3SCM
    .CARRIER_TYPE, --4SCM
    .CARRIER_NAME, --5SCM
    .CARRIER_PHONE, --6SCM
    .CARRIER_FAX, --7SCM
    .CARRIER_LOCATION_CODE, --8SCM
    .CARRIER_ACTIVE_INDICATOR, --9
    GETDATE()-1, --10
    '12/31/2199', --11
    GETDATE()) --12
    WHEN MATCHEDAND DCS.End_Date = '12/31/2199'
    AND DCS.ROW_UPDATE_TS <> SCM.ROW_UPDATE_TSTHEN UPDATE SET DCS
    .END_DATE = getdate()-2OUTPUT $Action, SCM
    .COMMON_CARRIER_CODE,SCM
    .CARRIER_NO,SCM
    .CARRIER_GROUP,SCM
    .CARRIER_TYPE,SCM
    .CARRIER_NAME,SCM
    .CARRIER_PHONE,SCM
    .CARRIER_FAX,SCM
    .CARRIER_LOCATION_CODE,SCM
    .CARRIER_ACTIVE_INDICATOR,
    GETDATE()-1,
    '12/31/2199',GETDATE()
    )
    AS [CHANGES] (
    [$action],Common_Carrier_Code
    , --1Carrier_NO
    , --2Carrier_Group
    , --3Carrier_Type
    , --4Carrier_Name
    , --5Carrier_Phone
    , --6Carrier_Fax
    , --7Carrier_Location_Code
    , --8Carrier_Active_Indicator --9
    --GETDATE()-1, --10
    --'12/31/2199', --11
    --GETDATE() --12)WHERE
    [CHANGES].[$Action] = 'UPDATE';
    ERROR I AM RECEIVING:Msg 213, Level 16, State 1, Line 1
    Column name or number of supplied values does not match table definition.
  2. FrankKalis Moderator

Share This Page