SQL 语法帮助，MERGE GROUP BY for Datawarehouse Dimensions

Question

我有一个 TSQL 代码，我想用它从源表更新/插入我的维度(SCD 类型 1)，下面是代码:

MERGE [dim].[CompanyTest] AS Target
USING [dbo].[DWUSD_LIVE] AS Source
    ON Target.Comp = Source.Comp
 WHEN MATCHED
    AND (ISNULL(Target.[Comp Name],'') <> ISNULL(Source.[Comp Name],'')
    OR ISNULL(Target.[Comp Description],'') <> ISNULL(Source.[Comp Description],'')
   )
THEN UPDATE SET
    [Comp Name] = Source.[Comp Name]
   ,[Comp Description] = Source.[Comp Description]
   ,LastUpdated = GetDate()
WHEN NOT MATCHED THEN
      INSERT (
        Comp
        ,[Comp Name]
        ,[Comp Description]
        ,LastUpdated
      ) VALUES (
         Source.Comp
        ,Source.[Comp Name]
        ,Source.[Comp Description]
        ,GetDate()
);

我的源表有:

[COMP] [COMP NAME] [COMP DESCRIPTION]
1,100,MyCompany,Service Provider
1,100,MyCompany,Service Provider
1,100,MyCompany,Service Provider
2,200,MyCompany,Service Provider
2,200,MyCompany,Service Provider
2,200,MyCompany,Service Provider
2,200,MyCompany,Service Provider

当我运行上面的 TSQL 一次时，我在我的维度中得到了这个:

[COMP] [COMP NAME] [COMP DESCRIPTION]
1,100,MyCompany,Service Provider
1,100,MyCompany,Service Provider
1,100,MyCompany,Service Provider
2,200,MyCompany,Service Provider
2,200,MyCompany,Service Provider
2,200,MyCompany,Service Provider
2,200,MyCompany,Service Provider

第一个问题是，当我尝试重新运行它时，我得到:

消息 8672，级别 16，状态 1，第 1 行MERGE 语句试图多次更新或删除同一行。当目标行匹配多个源行时会发生这种情况。 MERGE 语句不能多次更新/删除目标表的同一行。优化 ON 子句以确保目标行最多匹配一个源行，或使用 GROUP BY 子句对源行进行分组。

我知道我需要在语句中插入一个“GROUP BY”，我该怎么做才能只从源表中返回不同的行。

那么我的 Dimension 应该只有:

[COMP] [COMP NAME] [COMP DESCRIPTION]
1,100,MyCompany,Service Provider
2,200,MyCompany,Service Provider

Answer 1

这个很简单，在USING语句中使用派生表

MERGE [dim].[CompanyTest] AS Target
USING (
       SELECT DISTINCT *
       FROM [dbo].[DWUSD_LIVE]
       ) AS Source
...