Example of working around FK Constraint restrictions with a MERGE statement

WorkAroundConstraintsWithMerge.sql

--1: Create Temp table
CREATE TABLE #MyFactTable(
    ID INT NULL
    , CustomerName VARCHAR(100) NULL
    , SourceID INT NULL
    , OutputAction VARCHAR(100) NULL
);
 
--2: INSERT into the temp table instead of your normal target table
--   Merge query will be the same otherwise
INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction)
SELECT so.ID, so.CustomerName, so.SourceID, so.output_action
FROM (
    MERGE INTO dbo.MyFactTable AS t
    USING Staging.MyFactTable AS s
    ON ( s.ID = t.ID
        AND s.NewLoad = 0 )
        
    WHEN MATCHED AND ( s.SourceID  t.SourceID )
            AND s.NewLoad = 0            
    THEN UPDATE 
        SET RecordState = 0
        , UpdatedDate = getdate()
        
    WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN
        INSERT (ID, CustomerName, SourceID)
        VALUES (s.ID, s.CustomerName, s.SourceID)
        OUTPUT $action AS OutputAction
            , ID
            , CustomerName
            , SourceID
        ) AS so (OutputAction, ID, CustomerName, SourceID)
        WHERE OutputAction = 'UPDATE'  ;
 
--3: Perform the final insert into your target table
INSERT INTO MyFactTable (ID, CustomerName, SourceID)
SELECT DISTINCT ID, CustomerName, SourceID
FROM #MyFactTable ;
 
--4: Clean up your temp objects.
DROP TABLE #MyFactTable ;