tSQL MERGE STATEMENT - MANAGING A TYPE 2 SLOWLY CHANGING DIMENSION

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.