在SQL Server中使用MERGE语句管理渐变维度

Question

我正在尝试使用T-SQL构建一个慢速变化维度表，因为我注意到我不能在SSIS中使用SCD，因为我有大量数据。

我需要你的帮助

我的表格结构：

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 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;

到目前为止，上面的语句是正确的，但是下面的语句不起作用。

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

然而，我收到了这个错误消息：

消息10710，级别15，状态1，第15行
MERGE语句的"WHEN not MATCHED"子句中不允许"UPDATE"类型的操作。

我不知道如何解决这个错误消息。

Answer 1

您需要按照@marc_s 的建议，从WHEN NOT MATCHED子句中删除UPDATE语句

您可以根据所有这些条件进行Select并按摩currentflag、enddate，并将其作为源表，而不仅仅是Customer。

这是的片段

  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)