December 12, 2012 at 10:28 am
I'm working on a data migration project, and one of the tables I was importing gave me this error:
The INSERT statement conflicted with the FOREIGN KEY constraint... etc. I ran the test successfully before (same script and code, but failed when I added it to the larger import process).
I checked the results of the the Select portion of the SELECT INTO statement, and compared it with the look-up table, but there wasn't any mismatch. I disabled Triggers, and when I finally removed that column from the scenario, it just sprang up with the same error for another foreign key--which wasn't optional so I couldn't simply remove it.
I was reading through posts and had found a scenario where this error was being erroneously generated because of a totally unrelated scenario (the use of temporary tables in SQL Server 2005) but tried the work around for that problem in desperation--I added "ORDER BY 1" to the end of the SELECT statement. And it worked! A brief look at the results seem accurate. But why would this "fix" a Foreign Key constraint?
This is the support article that inspired the solution that I found elsewhere on this forum: http://support.microsoft.com/kb/977100
December 12, 2012 at 1:08 pm
So, are you performing this migration with SQL 2005 ? This is a SQL 2008 forum. However, the KB article clearly indicates what causes the problem - a bug in SQL 2005 in a MERGE JOIN scenario within the execution plan caiuses the index to be read in allocation order when it needs to be read it in key order. The workarounds solve the problem by forcing the order on the index (by using the ORDER BY clause), or by using a query hint to force a LOOP JOIN instead of a MERGE JOIN in the execution plan. There was no mention of a table needing to be a temp table, so I would expect the problem applied regardless of where the table was located. The KB fix was first released in CU 7 for SQL 2005 SP3, so what kind of help are you looking for here, and why is that KB article "unrelated" ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 5:47 pm
Sorry, because I AM using 2008, and I thought the issue I described was a temp table issue, but I guess not. I solved the problem using the same work-around as a hunch, and it worked, but if the problem is the same and the issue is present in 2008, then I guess it could apply... I just don't understand it yet.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply