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 161The 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 srcON 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.resaddr2OR tgt.address3 <> src.resaddr3 OR tgt.post_code <> src.respcode ) THEN UPDATESET 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 )VALUES(src.access_no,src.resaddr1,src.resaddr2,src.resaddr3,src.respcode,'R') 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';
Category
Proposed Solution
Benefits
Other Benefits
Please wait...