Managing Slowly Changing Dimension with MERGE Statement in SQL Server

Question

I am trying to build a Slow Changing Dimension Table with T-SQL since I notice I can not used the SCD in SSIS because I have a large data.

I need your help

My table structure:

CREATE TABLE [dbo].[Customer]
(
    [BusinessEntityID] [int] PRIMARY KEY NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [AddressLine1] [nvarchar](60) NULL,
    [AddressLine2] [nvarchar](60) NULL,
    [City] [nvarchar](30) NULL,
    [PostalCode] [nvarchar](15) NULL,
    [CountryRegionCode] [nvarchar](3) NULL,
    [Country] [nvarchar](50) NULL,
    [StateProvince] [nvarchar](50) NULL
)

CREATE TABLE [dbo].[DimCustomer]
(
    [CustomerKey] int IDENTITY (1,1) PRIMARY KEY CLUSTERED,
    [BusinessEntityID] int NOT NULL,
    [FirstName] nvarchar(50) NULL,
    [MiddleName] nvarchar(50) NULL,
    [LastName] nvarchar(50) NULL,
    [EmailAddress] nvarchar(50) NULL,
    [AddressLine1] nvarchar(60) NULL,
    [AddressLine2] nvarchar(60) NULL,
    [City] nvarchar(30) NULL,
    [PostalCode] nvarchar(15) NULL,
    [CountryRegionCode] nvarchar(3) NULL,
    [Country] nvarchar(50) NULL,
    [StateProvince] nvarchar(50) NULL,
    [StartDate] Datetime NULL,
    [EndDate] Datetime NULL,
    [CurrentFlag] Char(1)
)

Merge statement:

MERGE INTO dbo.DimCustomer Dest
USING dbo.Customer Src ON (Dest.BusinessEntityID = Src.BusinessEntityID)

WHEN MATCHED AND Dest.FirstName != Src.FirstName 
  THEN 
     UPDATE SET Dest.FirstName = Src.FirstName;

USE BART_DEV
GO
MERGE INTO dbo.DimCustomer Dest
USING dbo.Customer Src ON (Dest.BusinessEntityID = Src.BusinessEntityID)

WHEN MATCHED AND Dest.MiddleName != Src.MiddleName 
  THEN 
    UPDATE SET Dest.MiddleName = Src.MiddleName;

USE BART_DEV
GO
MERGE INTO dbo.DimCustomer Dest
USING dbo.Customer Src ON (Dest.BusinessEntityID = Src.BusinessEntityID)

WHEN MATCHED AND Dest.LastName != Src.LastName 
  THEN 
     UPDATE SET Dest.LastName = Src.LastName;

So far the above statement is working correctly however the bottom statement does not work.

USE BOB_DEV
GO
INSERT INTO dbo.DimCustomer ([BusinessEntityID],[FirstName],[MiddleName], [LastName],[AddressLine1], [AddressLine2], [City], [PostalCode], [CountryRegionCode], [Country], [StateProvince], [StartDate], [EndDate], [CurrentFlag])
   SELECT 
      [BusinessEntityID], [FirstName], [MiddleName], [LastName],
      [AddressLine1], [AddressLine2], [City], [PostalCode], [CountryRegionCode],
      [Country], [StateProvince], [StartDate], [EndDate], [CurrentFlag]
   FROM
      (MERGE dbo.DimCustomer TT
       USING dbo.Customer ST ON (TT.BusinessEntityID = ST.BusinessEntityID)

       WHEN NOT MATCHED THEN
          INSERT ([BusinessEntityID], [FirstName], [MiddleName], [LastName],
                  [AddressLine1], [AddressLine2], [City], [PostalCode],
                  [CountryRegionCode], [Country], [StateProvince],
                  [StartDate], [EndDate], [CurrentFlag])
          VALUES ([BusinessEntityID], [FirstName], [MiddleName], [LastName],
                  [AddressLine1], [AddressLine2], [City], [PostalCode],
                  [CountryRegionCode], [Country], [StateProvince],
                  '01/01/1900', NULL, '0')

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.AddressLine1, '') != ISNULL (ST.AddressLine1,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1 

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.AddressLine2, '') != ISNULL (ST.AddressLine2,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.City, '') != ISNULL (ST.City,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.PostalCode, '') != ISNULL (ST.PostalCode,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.CountryRegionCode, '') != ISNULL (ST.CountryRegionCode,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.Country, '') != ISNULL (ST.Country,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1

       WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.StateProvince, '') != ISNULL (ST.StateProvince,'')) 
         THEN 
           UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1

        OUTPUT $Action  Action_Taken, ST.BusinessEntityID, ST.FirstName, ST.MiddleName, ST.LastName, ST.AddressLine1, ST.AddressLine2, ST.City, ST.PostalCode, ST.CountryRegionCode, ST.Country, ST.StateProvince, GETDATE() AS EndDate, NULL AS EndDate, 'Y' AS CurrentFlag
)
AS MERGE_OUT 
WHERE MERGE_OUT.Action_Taken = 'UPDATE';
GO
SELECT * FROM dbo.DimCustomer

However, I am getting this error message :

Msg 10710, Level 15, State 1, Line 15
An action of type 'UPDATE' is not allowed in the 'WHEN NOT MATCHED' clause of a MERGE statement.

I don't know how to solve this error message.

Well, the error seems pretty clear: since you're **NOT** matching with an existing row in the target table, you **CANNOT** use the `UPDATE` statement - after all, there is no matching row to update! You need to use `INSERT` instead in this case! — marc_s, Sep 26 '14 at 19:27
Can you be more specific please? Can you show me an example using my sql statement? — Pabartho, Sep 26 '14 at 19:30
WHEN NOT MATCHED AND TT.CurrentFlag = 'Y' AND (ISNULL(TT.AddressLine1, '') != ISNULL (ST.AddressLine1,'')) THEN UPDATE SET TT.CurrentFlag = 'N', TT.EndDate = GETDATE() -1 — paparazzo, Sep 26 '14 at 19:33
Pretty simply: if you have `WHEN NOT MATCHED .....`, then you **must** have an `INSERT ......` after that - **NOT** an `UPDATE` since there is **nothing** there to update! See your own code - you have *plenty* of cases where you use `IF NOT MATCHED ......` and then you call `UPDATE` ..... fix these - all of them — marc_s, Sep 26 '14 at 19:35
Also, you will have another problem - according to the [MSDN documentation on `MERGE`](http://msdn.microsoft.com/en-us/library/bb510625.aspx): *The MERGE statement can have only one WHEN NOT MATCHED clause* ..... — marc_s, Sep 26 '14 at 19:38
That is the same statement I wrote above. What I am asking is to give the solution to my problem. How should my statement to written to work. I am basic in SQL. Thanks for your help. I need this to work really bad. — Pabartho, Sep 26 '14 at 19:39
Hi @marc_s, thank you for your feed back but still I don't know how to rewrite my statement the way you told me. Can you rewrite the first statement so that I can get it and apply to the rest. Thanks — Pabartho, Sep 26 '14 at 19:50
You should have **one** `WHEN NOT MATCHED` clause (you already have that) that does an `INSERT` if the row from the source doesn't exist in the target table yet, and you might also have **ONE** `WHEN MATCHED` clause that updates the existing row in the target table. You really can't just *rewrite* what you have - you need to re-design it to comply with the rules of the `MERGE` statement ... — marc_s, Sep 26 '14 at 20:33

radar · Answer 1 · 2014-09-26T20:25:27.640

you need to remove UPDATE statement from WHEN NOT MATCHED clause as suggested by @marc_s

you can do a Select and massage your currentflag, enddate based on all those conditions and make it as source table instead of just Customer.

here is the snippet

  USING 
   (
        select ST.[BusinessEntityID], ST.[FirstName], ST.[MiddleName], ST.[LastName],
              ST.[AddressLine1], ST.[AddressLine2], ST.[City], ST.[PostalCode],
              ST.[CountryRegionCode], ST.[Country], ST.[StateProvince],
              ST.[StartDate], 
              (CASE WHEN ST.CurrentFlag ='Y' AND (ISNULL(TT.AddressLine1, '') != ISNULL (ST.AddressLine1,''))  
                         OR
                         ST.CurrentFlag = 'Y' AND (ISNULL(TT.AddressLine2, '') != ISNULL (ST.AddressLine2,'')) 
                    THEN GETDATE() -1 ELSE ST.EndDate 
               .....
              ) as [EndDate],
              (CASE WHEN ST.CurrentFlag ='Y' AND (ISNULL(TT.AddressLine1, '') != ISNULL (ST.AddressLine1,''))
                     OR
                     ST.CurrentFlag = 'Y' AND (ISNULL(TT.AddressLine2, '') != ISNULL (ST.AddressLine2,'')) 
                    THEN 'N' ELSE ST.CurrentFlag 
                    END
              ) as CurrentFlag                       
        dbo.Customer ST 
        INNER JOIN DimCustomer TT
        ON TT.BusinessEntityID = ST.BusinessEntityID



   )
   ON (TT.BusinessEntityID = ST.BusinessEntityID)

@Pabartho, I see that you want insert currentFlags and enddate differently based on some conditions, your current source table is just Dbo.customer instead you can use a select like what I posted — radar, Sep 26 '14 at 20:16

Managing Slowly Changing Dimension with MERGE Statement in SQL Server

1 Answers1