Home Dashboard Directory Help
Search

Insert over DML is not allowed on a table with a foreign key constraint by BrentMcC


Status: 

Closed
 as Won't Fix Help for as Won't Fix


3
0
Sign in
to vote
Type: Suggestion
ID: 535753
Opened: 2/23/2010 3:27:32 PM
Access Restriction: Public
0
Workaround(s)
view

Description

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 )
THEN UPDATE
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 )
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';

Details
Sign in to post a comment.
Posted by Microsoft on 3/24/2011 at 2:21 PM
Hello Brent,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen this suggestion or respond to this message and we will take another look.

Jan
SQL Server Engine
Posted by Microsoft on 2/24/2010 at 10:53 AM
Hi Brent,
Thanks for your feedback. We will consider removing the restriction in a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.