I need to merge data from one table to another, but I have one value that comes from a different table

Question

This is my code

MERGE OrderDetailsStructure AS Target
USING SalesOrderData AS Source
ON Source.WebOrderNo=Target.WebOrderNo
WHEN NOT MATCHED BY TARGET
THEN
INSERT (WebOrderNo,OrderNo,ListTotal,
quantity,ExtendedPrice,[LineNo],Category,SSD,LorH,WorB,Species,FinishingOption,
Sheen,FinishingBrand,StockPaint,SSThick,BRD,TRD,CRD,CRD2,CRD3,CRP1,CRP2,CRP3,Section1,Section2,Section3,Section4,
RailConfig,PanelType,ItemText6,ShutterComments,LouverDirection)

VALUES (Source.WebOrderNo,Source.ListTotal,
Source.quantity,Source.ExtendedPrice,Source.[LineNo],Source.Category,Source.SSD,Source.LorH,Source.WorB,Source.Species,Source.FinishingOption,
Source.Sheen,Source.ColorName,Source.ColorNameStock,Source.[SS Thickness],Source.BRD,Source.TRD,Source.CRD,Source.CRD2,Source.CRD3,Source.CRP1,Source.CRP2,Source.CRP3,Source.Section1,Source.Section2,Source.Section3,Source.Section4,
Source.RailConfig,Source.PanelType,Source.LouverType,Source.Comments,Source.LouverDirection);

However, the second value after my insert statement, "OrderNo", will come from a third table. The third table also has the value WebOrderNo. How do I accomplish this?

*****Updated

So basically I need to merge the data from SalesOrderData(TableA) into OrderDetails (TableB) where it doesn't exist in TableB.

However, the field "OrderNo" is not in TableA. I cannot perform the merge without the "OrderNo" field because in TableB, "OrderNo" is the primary key.

So I have TableC that does contain the field "OrderNo" and the data from TableA has already been merged into TableC.

I can join on the field "WebOrderNo" from TableA to TableC.

Answer 1

I got it to work using an Inner Join. Here is my code

INSERT INTO TableB
   (Column1,Column2,....)

SELECT TableC.Column1, TableA.Column2,TableA.Column3....)

FROM TableA INNER JOIN
TableC ON TableA.LikeColumn = TableC.LikeColumn