• Each parent part number can have a one to many relationship with child part numbers
• The may be duplicate child part numbers for any one parent part number (This is for traceability, See ICN-1026-1212 example below). In these cases the child_start field for the original item will not change

• Brand new ps_parent number with a bunch of ps_child part numbers added in NewBOM
• A ps_parent item and all it's child items are deleted from CurrentSavedBOM
• ps_parent,ps_child and child_start match, but another field is different in NewBOM
• A ps_child item has been added or deleted from an existing ps_parent item in NewBOM

MERGE CurrentSavedBOM AS target
USING NewBOM AS source
ON target.ps_parent = source.ps_parent AND target.ps_child = source.ps_child AND target.child_start = source.child_start 
WHEN MATCHED AND target.ps_desc<>source.ps_desc
				OR target.child_desc<>source.child_desc
				OR target.ps_child_qty<>source.ps_child_qty
				OR target.child_end<>source.child_end
					THEN
		UPDATE SET
				target.ps_desc = source.ps_desc,
				target.child_desc = source.child_desc,
				target.ps_child_qty = source.ps_child_qty,
				target.child_end = source.child_end
OUTPUT $action,inserted.*,deleted.*;
GO

MERGE CurrentSavedBOM AS target
USING NewBOM AS source
ON target.ps_parent = source.ps_parent
WHEN NOT MATCHED BY TARGET THEN    
	INSERT (ps_parent, ps_desc, ps_child, child_desc, ps_child_qty, child_start, child_end)
	VALUES (source.ps_parent, source.ps_desc, source.ps_child, source.child_desc, source.ps_child_qty, source.child_start, source.child_end)
OUTPUT $action,inserted.*;