Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example of working around FK Constraint restrictions with a MERGE statement
--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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment