title: How to update and insert a new record on when matched condition in SQL Server
body: <p>I have written a stored procedure in SQL Server. Basically it tries to handle SCD2. Three things am trying to do:</p>
  
  <ol>
  <li>When not matched by target, insert the record</li>
  <li>When not matched by source, inactivate the active record(update)</li>
  <li>When matched, update the active record flag to N</li>
  </ol>
  
  <p>These 3 scenarios are being properly handled. </p>
  
  <p>My problem is when matched I need to insert a new record as well in source. I have some article suggesting to use <code>$output</code> clause and do insert on top of <code>MERGE</code>; I tried it but I get this error:</p>
  
  <blockquote>
    <p>Msg 356, Level 16, State 1, Procedure myscd2, Line 17<br>
    The target table 'dbo.dimproducts' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK_FactSalesOrders_DimProducts'.</p>
  </blockquote>
  
  <p>Please see my code below and let me know, what can I do to resolve this issue:</p>
  
  <pre><code>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';
  </code></pre>