Monday, January 17, 2011

SQL 2008 Merge and PK/FK Constraints

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.
  1. Create a temp table that matches the definition of the table into which you want to perform your insert.
  2. 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.
  3. 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.
   1: --1: Create Temp table
   2: CREATE TABLE #MyFactTable(
   3:     ID INT NULL
   4:     , CustomerName VARCHAR(100) NULL
   5:     , SourceID INT NULL
   6:     , OutputAction VARCHAR(100) NULL
   7: );
   8:  
   9: --2: INSERT into the temp table instead of your normal target table
  10: --   Merge query will be the same otherwise
  11: INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction)
  12: SELECT so.ID, so.CustomerName, so.SourceID, so.output_action
  13: FROM (
  14:     MERGE INTO dbo.MyFactTable AS t
  15:     USING Staging.MyFactTable AS s
  16:     ON ( s.ID = t.ID
  17:         AND s.NewLoad = 0 )
  18:         
  19:     WHEN MATCHED AND ( s.SourceID <> t.SourceID )
  20:             AND s.NewLoad = 0            
  21:     THEN UPDATE 
  22:         SET RecordState = 0
  23:         , UpdatedDate = getdate()
  24:         
  25:     WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN
  26:         INSERT (ID, CustomerName, SourceID)
  27:         VALUES (s.ID, s.CustomerName, s.SourceID)
  28:         OUTPUT $action AS OutputAction
  29:             , ID
  30:             , CustomerName
  31:             , SourceID
  32:         ) AS so (OutputAction, ID, CustomerName, SourceID)
  33:         WHERE OutputAction = 'UPDATE'  ;
  34:  
  35: --3: Perform the final insert into your target table
  36: INSERT INTO MyFactTable (ID, CustomerName, SourceID)
  37: SELECT DISTINCT ID, CustomerName, SourceID
  38: FROM #MyFactTable ;
  39:  
  40: --4: Clean up your temp objects.
  41: DROP TABLE #MyFactTable ;

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.

5 comments:

  1. Thanks for the solution.

    My only suggestion would be to replace the CREATE TABLE for the temporary table with

    SELECT * into #MyFactTable
    FROM MyFactTable
    WHERE
    1=0

    so that you don't have to recreate the full table definition in the proc.

    Cheers,
    Dan

    ReplyDelete
  2. Dan,
    I've tried to be a bit better about explicitly defining my tables when creating various stored procedures. That practice has saved me from surprises later on when something small changes in a table definition or something similar. I've definitely used that as a shortcut in the past, but try to avoid it inside of a stored proc or similar object.

    It's a great shortcut for creating a table structure, though. That WHERE 1=2 or 1=0 shortcut helps me quite a bit when I am in the middle of a lot of ad-hoc work.

    I hope that it helps you a bit. I know it surprised me quite a bit that this didn't just work and I figured there had to be a decent workaround. (or at least _a_ workaround)

    ReplyDelete
    Replies
    1. Well i think it's a good syntax because you're always guaranteed that the temp table exactly matches the final table. It might happen that small changes are handled in the "official" table (because they have an impact in some frontend/reports, for example) but might be overlooked in the #temptable.
      Btw, just found this post having the same "SCD-2 nested insert" issue, still in 2016... :-/

      Delete
  3. Hi Peter, In my "MyFactTable" I have IsCurrent = 0 or 1 for scd type 2 and some of records Iscurrent =1 or IsCurrent = 0. After I used the code I got all Iscurrent = 0.

    What did I do wrong ? Thank you
    WHEN MATCHED AND IsCurrent = 1
    and(t.[FirstName] <> s.[FirstName] )
    THEN UPDATE SET T.IsCurrent = 0, T.[SystemDateUpdated] = getdate()

    ReplyDelete
  4. Without knowing more about your structure and data, this isn't really something I can answer. I'd highly recommend the TSQL area on stackexchange.com to get a better answer with this. You can also try using the #sqlhelp tag on Twitter, though you'll probably still want to post your code snippets and schema for repro somewhere and StackExchange or StackOverflow are great places for that.

    ReplyDelete