Как обновить и вставить новую запись в случае соответствия условию в SQL Server

Question

Я написал хранимую процедуру в SQL Server. В основном это пытается обработать SCD2. Три вещи, которые я пытаюсь сделать:

1. Если не соответствует цели, вставьте запись
2. Если источник не соответствует, деактивируйте активную запись (обновление)
3. При совпадении обновите флаг активной записи на N

Эти 3 сценария правильно обрабатываются.

Моя проблема заключается в том, что при совпадении мне нужно также вставить новую запись в исходный код. У меня есть статья, предлагающая использовать $output пункт и вставьте сверху MERGE; Я попробовал это, но я получаю эту ошибку:

Сообщение 356, уровень 16, состояние 1, процедура myscd2, строка 17
Таблица назначения "dbo.dimproducts" оператора INSERT не может находиться ни по одной из сторон отношения (первичный ключ, внешний ключ), когда предложение FROM содержит вложенный оператор INSERT, UPDATE, DELETE или MERGE. Обнаружено ссылочное ограничение "FK_FactSalesOrders_DimProducts".

Пожалуйста, посмотрите мой код ниже и дайте мне знать, что я могу сделать, чтобы решить эту проблему:

begin
    insert into dbo.dimproducts(ProductID, ProductName, StandardListPrice, ProductSubCategoryID, ProductSubCategoryName, ProductCategoryID, ProductCategoryName, active_flag)   
        select 
            productid, name, ListPrice, 
            productsubcategoryid, ProductSubCategoryName,
            ProductCategoryID, ProductCategoryName, Active_Flag 
        from
            (merge dbo.dimproducts as tgt
             using AdventureWorks_Basics.dbo.Products as src on (tgt.productid = src.productid)
             when matched and src.name != tgt.productname and tgt.active_flag = 'Y'
                then 
                   update set tgt.active_flag = 'N'
             when not matched by target
                then 
                   insert(ProductID, ProductName, StandardListPrice, ProductSubCategoryID, ProductSubCategoryName, ProductCategoryID, ProductCategoryName, active_flag)
                   values (src.productid, src.name, src.listprice, 100, 'ABC', 101, coalesce(src.productline, 'XYZ'), 'Y')
             when not matched by source
                then 
                   update set tgt.active_flag = 'N'
             OUTPUT $action AS Action, src.productid, src.name, src.listprice, 100 as productsubcategoryid, 'ABC' as ProductSubCategoryName, 101 as ProductCategoryID, coalesce(src.productline, 'XYZ') as ProductCategoryName, 'Y' as Active_Flag) AS MergeOutput
        where
            MergeOutput.Action = 'UPDATE';

Answer 1

http://www.sqlservercentral.com/articles/MERGE/73805/

Медленно меняющиеся измерения с использованием T-SQL MERGE Адам Аспин, 2011/06/20
See section: Тип 2 SCD - Храните историю предыдущих атрибутов как отдельные записи, указывайте даты, когда они были действительными, и отмечайте текущую действительную запись.

это в основном ответ М.Али с большим количеством дополнительной информации, а также информацией о SCD ​​1, 3 и 4:

Answer 2

Существует ограничение на OUTPUT предложение о том, что цель не может иметь ограничение FOREIGN KEY или на нее не может ссылаться ограничение FOREIGN KEY. Отсюда и ошибка, которую вы получаете.

Однако вы можете сделать следующее, вставить данные во временную таблицу из предложения вывода и затем выбрать из временной таблицы, чтобы вставить ее в целевую таблицу.

Предупреждение, я все равно советую вам избегать использования MERGE Заявление и прочитать статью по причинам.

CREATE TABLE #dimproducts (
 Act                        VARCHAR(10)
,ProductID                  INT
,ProductName                VARCHAR(100)
,StandardListPrice          INT
,ProductSubCategoryID       INT
,ProductSubCategoryName     VARCHAR(100)
,ProductCategoryID          INT
,ProductCategoryName        VARCHAR(100)
,active_flag                VARCHAR(1));

merge dbo.dimproducts as tgt
    using  AdventureWorks_Basics.dbo.Products as src    on (tgt.productid = src.productid)

when matched and src.name != tgt.productname and tgt.active_flag = 'Y'then 
    update set tgt.active_flag = 'N'

when not matched by target then 
    insert(ProductID,ProductName,StandardListPrice,ProductSubCategoryID,ProductSubCategoryName,ProductCategoryID,ProductCategoryName,active_flag)
    values(src.productid,src.name,src.listprice,100,'ABC',101,coalesce(src.productline,'XYZ'),'Y')

when not matched by source then
    update set tgt.active_flag = 'N'

    OUTPUT $action AS Action, src.productid,src.name,src.listprice,100 as productsubcategoryid,'ABC' as ProductSubCategoryName
                    ,101 as ProductCategoryID,coalesce(src.productline,'XYZ') as ProductCategoryName,'Y' as Active_Flag
    INTO #dimproducts (Act,ProductID,ProductName,StandardListPrice,ProductSubCategoryID,ProductSubCategoryName,ProductCategoryID,ProductCategoryName,active_flag);

INSERT INTO dbo.dimproducts (productid,name,ListPrice,productsubcategoryid,ProductSubCategoryName,ProductCategoryID,ProductCategoryName,Active_Flag)
select productid,name,ListPrice,productsubcategoryid,ProductSubCategoryName,ProductCategoryID,ProductCategoryName,Active_Flag 
FROM #dimproducts
WHERE Act = 'UPDATE';