Merge with multiple updates and inserts

Question

Basically I have a SQL Server 2008 R2 database. The database has a table called Node and a link. The link contains StartNodeId and EndNodeId related to the identifier in Node. The database also requires a table of links between Node and Link for faster verification, for example, is the Node associated with this link or which nodes are associated with this link. The link table contains the key Identity, NodeId and LinkId. My problem is that when I do my insertions, I try to use merge instructions that seem to not be able to do what I'm trying

When i tried

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; 

I get the error message "An action of type WHEN MATCHED cannot appear more than once in the UPDATE clause of the MERGE statement"

If I try to insert the start nodes and end nodes separately, for example.

  --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; 

In the end, I delete the links (not surprisingly), so basically I was wondering if anyone knows of a good way to do this? If possible, I would like to be able to do this still using the merge operator

thanks

Edit: I found another way to merge this data using a different source, now the problem is resolved.

Answer 1

I might have missed something, but

The error message complains that you cannot have multiple WHEN MATCHED so you can convert

 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 

to

 WHEN MATCHED AND TARGET.NodeId IN (Source.StartNodeId,Source.EndNodeId) THEN UPDATE SET TARGET.NodeId = CASE WHEN TARGET.NodeId = Source.StartNodeId THEN SOURCE.StartNodeId ELSE Source.EndNodeId END, TARGET.LinkId = SOURCE.Id 

But since the first CASE branch is hit when TARGET.NodeId = Source.StartNodeId and also sets TARGET.NodeId = Source.StartNodeId and similarly for the second branch, this simplifies

 WHEN MATCHED AND TARGET.NodeId IN (Source.StartNodeId,Source.EndNodeId) THEN UPDATE SET TARGET.LinkId = SOURCE.Id