I am trying work out with MERGE statment to Insert / Update Dimension Table of Type SCD2
My source is a Table var to Merge with Dimension table.
My Merget statement is throwing an error as:
The target table 'DM.DATA_ERROR.ERROR_DIMENSION' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK_ERROR_DIMENSION_to_AUDIT_CreatedBy'.
My Merge Statement:
DECLARE @DATAERROROBJECT AS [ERROR_DIMENSION]
INSERT INTO DM.DATA_ERROR.ERROR_DIMENSION
SELECT ERROR_CODE,
DATA_STREAM_ID,
[ERROR_SEVERITY],
DATA_QUALITY_RATING,
ERROR_LONG_DESCRIPTION,
ERROR_DESCRIPTION,
VALIDATION_RULE,
ERROR_TYPE,
ERROR_CLASS,
VALID_FROM,
VALID_TO,
CURR_FLAG,
CREATED_BY_AUDIT_SK,
UPDATED_BY_AUDIT_SK
FROM (MERGE DM.DATA_ERROR.ERROR_DIMENSION ED USING @DATAERROROBJECT OBJ
ON(ED.ERROR_CODE = OBJ.ERROR_CODE AND ED.DATA_STREAM_ID = OBJ.DATA_STREAM_ID) WHEN NOT MATCHED THEN INSERT VALUES(
OBJ.ERROR_CODE
,OBJ.DATA_STREAM_ID
,OBJ.[ERROR_SEVERITY]
,OBJ.DATA_QUALITY_RATING
,OBJ.ERROR_LONG_DESCRIPTION
,OBJ.ERROR_DESCRIPTION
,OBJ.VALIDATION_RULE
,OBJ.ERROR_TYPE
,OBJ.ERROR_CLASS
,GETDATE()
,'9999-12-13'
,'Y'
,1
,1
) WHEN MATCHED AND ED.CURR_FLAG = 'Y'
AND ( ED.[ERROR_SEVERITY] <> OBJ.[ERROR_SEVERITY]
OR ED.[DATA_QUALITY_RATING] <> OBJ.[DATA_QUALITY_RATING]
OR ED.[ERROR_LONG_DESCRIPTION] <> OBJ.[ERROR_LONG_DESCRIPTION]
OR ED.[ERROR_DESCRIPTION] <> OBJ.[ERROR_DESCRIPTION]
OR ED.[VALIDATION_RULE] <> OBJ.[VALIDATION_RULE]
OR ED.[ERROR_TYPE] <> OBJ.[ERROR_TYPE]
OR ED.[ERROR_CLASS] <> OBJ.[ERROR_CLASS] ) THEN UPDATE SET ED.CURR_FLAG = 'N', ED.VALID_TO = GETDATE()
OUTPUT $ACTION ACTION_OUT,
OBJ.ERROR_CODE ERROR_CODE,
OBJ.DATA_STREAM_ID DATA_STREAM_ID,
OBJ.[ERROR_SEVERITY] [ERROR_SEVERITY],
OBJ.DATA_QUALITY_RATING DATA_QUALITY_RATING,
OBJ.ERROR_LONG_DESCRIPTION ERROR_LONG_DESCRIPTION,
OBJ.ERROR_DESCRIPTION ERROR_DESCRIPTION,
OBJ.VALIDATION_RULE VALIDATION_RULE,
OBJ.ERROR_TYPE ERROR_TYPE,
OBJ.ERROR_CLASS ERROR_CLASS,
GETDATE() VALID_FROM,
'9999-12-31' VALID_TO,
'Y' CURR_FLAG,
555 CREATED_BY_AUDIT_SK,
555 UPDATED_BY_AUDIT_SK
) AS MERGE_OUT WHERE MERGE_OUT.ACTION_OUT = 'UPDATE';
What am i doing wrong ?
