April 27, 2009 at 11:19 am
USING SQL Server 2008
Overview of what I am trying to accomplish: But not with this schema but with my own:
Handle the Type 2 Changes
Now 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, --1
Carrier_NBR, --2
Carrier_Group, --3
Carrier_Type, --4
Carrier_Name, --5
Carrier_Phone, --6
Carrier_Fax, --7
Carrier_Location_Code, --8
Carrier_Active_Indicator, --9
Begin_Date, --10
End_Date, --11
Load_Date --12
)
SELECT
Common_Carrier_Code, --1
Carrier_NO, --2
Carrier_Group, --3
Carrier_Type, --4
Carrier_Name, --5
Carrier_Phone, --6
Carrier_Fax, --7
Carrier_Location_Code, --8
Carrier_Active_Indicator, --9
GETDATE()-1, --10
'12/31/2199', --11
GETDATE() --12
FROM
(
MERGE DIMENSION_Carrier_SCD DCS USING STAGE_Carrier_Master SCM
ON
(
DCS.Common_Carrier_Code = SCM.Common_Carrier_Code)
WHEN
NOT MATCHED THEN
INSERT VALUES
(
SCM.COMMON_CARRIER_CODE, --1
SCM.CARRIER_NO, --2
SCM.CARRIER_GROUP, --3
SCM.CARRIER_TYPE, --4
SCM.CARRIER_NAME, --5
SCM.CARRIER_PHONE, --6
SCM.CARRIER_FAX, --7
SCM.CARRIER_LOCATION_CODE, --8
SCM.CARRIER_ACTIVE_INDICATOR, --9
GETDATE()-1, --10
'12/31/2199', --11
GETDATE()) --12
WHEN MATCHED
AND DCS.End_Date = '12/31/2199'
AND DCS.ROW_UPDATE_TS <> SCM.ROW_UPDATE_TS
THEN UPDATE SET
DCS.END_DATE = getdate()-2
OUTPUT $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, --1
Carrier_NO, --2
Carrier_Group, --3
Carrier_Type, --4
Carrier_Name, --5
Carrier_Phone, --6
Carrier_Fax, --7
Carrier_Location_Code, --8
Carrier_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.
May 4, 2009 at 5:50 pm
Hi,
Check this ([$action] in your Merge code. That should help you fix this error.
🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply