We ran into this issue a while back. SQL 2008 has a “feature” that will not allow inserts into a table on either side of a Foreign Key relationship. If you attempt to insert into a table involved in the FK relationship, you get an error something like:
The target table ‘TableName’ 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 ‘ConstraintName’.
This is documented in Connect 435031. There is one workaround documented in the ticket – drop the FK constraint, run the MERGE query, then re-create the constraint. Happily, this bug is still open as the requirement to drop a constraint just to run a MERGE statement kind of defeats the purpose of putting a constraint on the table in the first place. While dropping the constraints will work, there is another possible workaround. I was able to run this without any issues while leaving my FK Constraint in place.
- Create a temp table that matches the definition of the table into which you want to perform your insert.
- Instead of inserting into your base table like you normally would in a merge, write your code to INSERT #Tablename at the beginning of the MERGE process. You’ll still use your main table and staging table inside the MERGE INTO … USING query.
- After the Merge, insert into the main table using the values in your Temp table.
Here’s a shell of an example. Line 11 below contains the main difference to the MERGE query from a “normal” MERGE.
I’d love to hear if anyone else has a workaround for this that doesn’t involve dropping and recreating the FK constraint. If this is an issue for you, please vote for the Connect Ticket.