The INSERT OVER DML functionality is not allowed when the target table of the insert statement is on either side of a PK/FK relationship. The following error is produced when trying to run the INERT INTO with a MERGE statement...
Msg 356, Level 16, State 1, Procedure usp_load_customer_table, Line 161
The target table 'crm.customer_address' 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_CUSTOMER_CUSTADD_C_CUSTOMER'.
This is my code...
INSERT INTO crm.customer_address (access_no, address1, address2, address3, post_code, address_type, international_address, end_date)
SELECT access_no, address1, address2, address3, post_code, address_type, international_address, CAST(GETDATE() AS DATE) FROM (
MERGE INTO crm.customer_address AS tgt USING staging.stage_customer AS src
ON tgt.access_no = src.access_no and tgt.address_type = 'R' AND tgt.end_date IS NULL WHEN MATCHED AND ( tgt.address1 <> src.resaddr1 OR tgt.address2 <> src.resaddr2
OR tgt.address3 <> src.resaddr3 OR tgt.post_code <> src.respcode )
SET tgt.address1 = src.resaddr1,
tgt.address2 = src.resaddr2,
tgt.address3 = src.resaddr3,
tgt.post_code = src.respcode WHEN NOT MATCHED THEN INSERT (
access_no, address1, address2, address3, post_code, address_type )
OUTPUT $ACTION, src.access_no, DELETED.address1, DELETED.address2, DELETED.address3, DELETED.post_code, DELETED.address_type, DELETED.international_address)
CHANGES (ACTION, access_no, address1, address2, address3, post_code, address_type, international_address)
WHERE ACTION = 'update';