title: Merge into with multiple updates and inserts
body: <p>Basically I have a SQL Server 2008 R2 database. The database has a table called Node and Link. Link contains a StartNodeId and EndNodeId relating to a Id in Node. The database also requires a Link table between Node and Link for quicker checking of say, is this Node related to this Link or which Nodes are related to this Link. The Link table contains a Identity key, NodeId and LinkId. My problem is when I am doing my inserts I am trying to use merge statements which do not seem to be able to do what I am trying</p>
  
  <p>When I tried</p>
  
  <pre><code>MERGE INTO [RoadRoutingDatabase].[dbo].[NodeToLink] AS TARGET
  USING (SELECT Id, StartNodeId, EndNodeId FROM [RoadRoutingDatabase].[dbo].[Link]) AS SOURCE
  ON (TARGET.LinkId = SOURCE.Id)
  WHEN MATCHED AND TARGET.NodeId = Source.StartNodeId THEN
      UPDATE SET TARGET.NodeId = SOURCE.StartNodeId,
                 TARGET.LinkId = SOURCE.Id
  WHEN MATCHED AND TARGET.NodeId = Source.EndNodeId THEN
      UPDATE SET TARGET.NodeId = SOURCE.EndNodeId,
                 TARGET.LinkId = SOURCE.Id
  WHEN NOT MATCHED BY TARGET AND TARGET.NodeId = Source.StartNodeId THEN
      INSERT (LinkId, NodeId)
      VALUES (SOURCE.Id, SOURCE.StartNodeId)
  WHEN NOT MATCHED BY TARGET AND TARGET.NodeId = Source.EndNodeId THEN
      INSERT (LinkId, NodeId)
      VALUES (SOURCE.Id, SOURCE.EndNodeId)
  WHEN NOT MATCHED BY SOURCE THEN
      DELETE;
  </code></pre>
  
  <p>I get the error message "An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement"</p>
  
  <p>If I try inserting Start Nodes and End Nodes seperatly e.g.</p>
  
  <pre><code>    --Insert Start Node To Link Relationships
      MERGE INTO [RoadRoutingDatabase].[dbo].[NodeToLink] AS TARGET
      USING (SELECT Id, StartNodeId FROM [RoadRoutingDatabase].[dbo].[Link]) AS SOURCE
      ON (TARGET.NodeId = SOURCE.StartNodeId AND TARGET.LinkId = SOURCE.Id)
      WHEN MATCHED THEN
          UPDATE SET TARGET.NodeId = SOURCE.StartNodeId,
                     TARGET.LinkId = SOURCE.Id
      WHEN NOT MATCHED BY TARGET THEN
          INSERT (LinkId, NodeId)
          VALUES (SOURCE.Id, SOURCE.StartNodeId)
      WHEN NOT MATCHED BY SOURCE THEN
          DELETE;
  
      --Insert End Node To Link Relationships
      MERGE INTO [RoadRoutingDatabase].[dbo].[NodeToLink] AS TARGET
      USING (SELECT Id, EndNodeId FROM [RoadRoutingDatabase].[dbo].[Link]) AS SOURCE
      ON (TARGET.NodeId = SOURCE.EndNodeId AND TARGET.LinkId = SOURCE.Id)
      WHEN MATCHED THEN
          UPDATE SET TARGET.NodeId = SOURCE.EndNodeId,
                     TARGET.LinkId = SOURCE.Id
      WHEN NOT MATCHED BY TARGET THEN
          INSERT (LinkId, NodeId)
          VALUES (SOURCE.Id, SOURCE.EndNodeId)
      WHEN NOT MATCHED BY SOURCE THEN
          DELETE;
  </code></pre>
  
  <p>I end up with links being deleted (not surprising) so basically I was wondering if anyone knew of a good way of doing this? If possible I would like to be able to do it still using a merge statement</p>
  
  <p>Thanks</p>
  
  <p><strong>Edit:</strong> I have found a different way of merging this data using a different source, the problem is now solved.</p>