title: SQL Server : Merge and Output
body: <p>I'm attempting to simplify some domain logic, rather then separate queries was attempting to utilize a merge to load a single query file as a resource within my application.  But I appear to run into the:</p>
  <blockquote>
  <p>The merge statement conflicted with the foreign key constraint</p>
  </blockquote>
  <p>I'll create a smaller example to demonstrate my issue:</p>
  <p>Table One: &quot;Customer&quot;</p>
  <ul>
  <li>Id</li>
  <li>Other columns...</li>
  </ul>
  <p>Table Two: &quot;Rates&quot;</p>
  <ul>
  <li>Id</li>
  <li>CustomerId   &lt;-- foreign key</li>
  <li>Other columns...</li>
  </ul>
  <p>In theory, if a customer is created it should leverage the id created for the second merge in rates.  If an update for customer occurs, then the id would then be used for the second merge or simply used passed parameter from code.</p>
  <pre><code>DECLARE @Identifier INT;
  DECLARE @CustomerAudit TABLE (
            Action NVARCHAR(MAX)
       ,    Id INT
  );
  
  MERGE INTO [web].[Customers] AS destination
  USING (VALUES (@Id, @OtherColumns)) AS source (Id, OtherColumns)
        ON (@Id = destination.[Id])
  
  WHEN NOT MATCHED 
      THEN 
          INSERT (OtherColumns) VALUES (@OtherColumns)
  
  WHEN MATCHED 
      THEN 
          UPDATE 
          SET destination.[OtherColumns] = @OtherColumns
  
  OUTPUT $action, inserted.[Id] INTO @CustomerAudit
  
  
  SELECT @Identifier = [Id] 
  FROM @CustomerAudit 
  WHERE @Identifier = Id
  
  MERGE INTO [web].[Rates] AS destination
  USING (VALUES (@Id, @Customer, @OtherColumns)) AS source (Id)
        ON (@Id = destination.[Id] AND COALESCE(@Identifier, @Customer) = destination.[Customer])
  
  WHEN NOT MATCHED 
      THEN 
          INSERT (Customer, OtherColumns) 
          VALUES (COALESCE(@Identifier, @Customer), @OtherColumns)
  
  WHEN MATCHED 
      THEN 
          UPDATE SET destination.[OtherColumns] = @OtherColumns
  
  OUTPUT $action, inserted.*;
  DELETE FROM @CustomerAudit;
  </code></pre>
  <p>The other columns have been truncated for brevity, but should convey intent.  The focus should be on the index constraints.  If I query the <code>@CustomerAudit</code> (not as a declared table but a full table) I can clearly see the inserted record with a match to the source.  But the second merge appears to have a key constraint issue.  Though I see the key in the table.</p>