September 15, 2010 at 11:02 am
The developer DBA and I killed an hour last night trying to make this work and failed. I showed it to another production DBA today and they too were flummoxed. Four servers, four databases total, all on SQL Server 2005, max SP level was 3 with no cumulative updates.
Here's the situation. We have an empty staging table we are trying to load. The staging table has a foreign key relationship to another table. The key is a composite of two fields, a varchar(8) and an int. The other table in the relationship doesn't have a primary key, but the fields referenced by the foreign key are in a unique clustered index. We are trying to load the table with rows, and to ensure that the foreign key is not messed up, we take the data we are loading (from a work DB on the same server) and inner join it to the remote table on the foreign key so only rows that are validly selected in an inner join get sumbitted to the INSERT.
No dice. Foreign key violation. We tried to create a temp table, use a cursor, all sorts of things. It's to the point where I selected a single row that I know is in the source table and joins properly to the referenced table, so that the value is there. I then try to insert just that one row into the staging table. Nope, foreign key violation, even though I just proved that the row existed in the unique index being referenced.
We finally got the data to insert using a CTE like this:
WITH Contracts
AS (SELECT
ROW_NUMBER() OVER (PARTITION BY TRADING_PARTNER_ID,CORP_ITEM_NUM ORDER BY ROW_UPDATE_STP DESC, ContractID DESC) RowNumber,
CORP_ITEM_NUM,
TRADING_PARTNER_ID,
ContractID
FROM
WorkDB.dbo.ContractItems_Staging cs WITH (NOLOCK)
WHERE
EXISTS ( SELECT
1
FROM
dbo.CUSTOMER_CATALOG cc WITH (NOLOCK)
WHERE
cc.CORP_ITEM_NUM = cs.CORP_ITEM_NUM AND
cc.TRADING_PARTNER_ID = cs.TRADING_PARTNER_ID ))
INSERT INTO #ContractItems -- a temp table we made
(
Trading_Partner_ID,
Corp_Item_Num,
ContractID
)
SELECT
TRADING_PARTNER_ID,
CORP_ITEM_NUM,
ContractID
FROM
Contracts c
WHERE
RowNumber = 1 ;
INSERT INTO dbo.ContractItems
(
Trading_Partner_ID,
Corp_Item_Num,
ContractID,
IsRebateEligible,
ChangedDate,
ChangedUser
)
SELECT
Trading_Partner_ID,
Corp_Item_Num,
ContractID,
0,
GETDATE(),
'WARP'
FROM
#ContractItems cs
WHERE
ContractID > 0 AND
NOT EXISTS ( SELECT
1
FROM
dbo.ContractItems ci
WHERE
ci.Corp_Item_Num = cs.Corp_Item_Num AND
ci.Trading_Partner_ID = cs.Trading_Partner_ID ) ;
But I can't explain why a simple INSERT....SELECT would not work and this had to be used. Three people looked this over pretty well, and all agree we are doing it "by the book". Is there some obscure setting we are missing, is this a bug, or what? I don't think it's a code issue as we have done this over and over throughout the years and all three of us agree that the code is valid and should work.
September 15, 2010 at 1:02 pm
I am still perplexed by this one. For clarity, here's a rundown of the situation.
This select against the staging table returns one row.
select * from WorkDB.dbo.ContractItems_Staging cc WHERE
cc.Trading_Partner_ID = 5 AND
cc.corp_item_num = 1000074
This select against the destination table returns zero rows.
select * from dbo.ContractItems cc WHERE
cc.Trading_Partner_ID = 5 AND
cc.corp_item_num = 1000074
This select against the table referenced by the foreign key returns one row.
select * from dbo.CUSTOMER_CATALOG cc WHERE
cc.Trading_Partner_ID = 5 AND
cc.corp_item_num = 1000074
To prove that the data is the same, I joined the staging table to the table referenced by the foreign key. This returns one row.
select * from WorkDB.dbo.ContractItems_Staging cs
inner join CUSTOMER_CATALOG cc on
cc.CORP_ITEM_NUM = cs.Corp_Item_Num AND
cc.TRADING_PARTNER_ID = cs.Trading_Partner_ID
where
cc.Trading_Partner_ID = 5 AND
cc.corp_item_num = 1000074
And for reference this is the foreign key code:
ALTER TABLE [dbo].[ContractItems] WITH CHECK ADD CONSTRAINT [FK_ContractItems_CUSTOMER_CATALOG] FOREIGN KEY([Corp_Item_Num], [Trading_Partner_ID])
REFERENCES [dbo].[CUSTOMER_CATALOG] ([CORP_ITEM_NUM], [TRADING_PARTNER_ID])
GO
ALTER TABLE [dbo].[ContractItems] CHECK CONSTRAINT [FK_ContractItems_CUSTOMER_CATALOG]
GO
Given this background, I see no reason why this would fail:
INSERT INTO dbo.ContractItems
(
Trading_Partner_ID,
Corp_Item_Num,
ContractID,
IsRebateEligible,
ChangedDate,
ChangedUser
)
SELECT
cs.Corp_Item_Num,
cs.Trading_Partner_ID,
ContractID,
0 IsRebateEligible,
GETDATE() ChangedDate,
SUSER_SNAME() ChangedUser
FROM
WorkDB.dbo.ContractItems_Staging cs
INNER JOIN dbo.CUSTOMER_CATALOG cc
ON cs.Corp_Item_Num = cc.CORP_ITEM_NUM AND
cs.Trading_Partner_ID = cc.TRADING_PARTNER_ID
WHERE
cc.Trading_Partner_ID = 5 AND
cc.corp_item_num = 1000074
But it DOES fail with this:
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ContractItems_CUSTOMER_CATALOG". The conflict occurred in database "...", table "dbo.CUSTOMER_CATALOG".
The statement has been terminated.
I did a DBCC CHECKDB on the database with no errors.
What the heck am I missing here????
September 15, 2010 at 1:37 pm
INSERT INTO dbo.ContractItems
(
Trading_Partner_ID,
Corp_Item_Num,
ContractID,
IsRebateEligible,
ChangedDate,
ChangedUser
)
SELECT
cs.Corp_Item_Num,
cs.Trading_Partner_ID,
ContractID,
0 IsRebateEligible,
GETDATE() ChangedDate,
SUSER_SNAME() ChangedUser
Are the items in bold in the wrong order?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 1:41 pm
I think I am going to kill that developer tomorrow....
(I say this knowing that he reads this forum and probably will see this before we talk 😛 )
All I can say is that three people missed it and so I can't say I am a total idiot.
September 15, 2010 at 1:47 pm
jeff.mason (9/15/2010)
I think I am going to kill that developer tomorrow....
Why wait that long? A 5 minute start is all he should get... :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 1:49 pm
He's in training today so I have to wait. Actually I will be happy to get him to buy lunch or something....
September 15, 2010 at 1:55 pm
jeff.mason (9/15/2010)
He's in training today so I have to wait. Actually I will be happy to get him to buy lunch or something....
I strongly recommend pork chops for lunch. 😉 Heh... for you, too! You all missed it. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2010 at 1:59 pm
Yeah, I know, I feel stupid too. What can I say, it was 11:30 PM, we are tired, and stuck in a rut for troubleshooting. We attacked the WHERE clause and did every form of INSERT we could think of, but no one noticed that the staging table and the destination table were in different column orders. Hopefully it happens to all of us sometime.
September 15, 2010 at 2:03 pm
jeff.mason (9/15/2010)
Yeah, I know, I feel stupid too. What can I say, it was 11:30 PM, we are tired, and stuck in a rut for troubleshooting. We attacked the WHERE clause and did every form of INSERT we could think of, but no one noticed that the staging table and the destination table were in different column orders. Hopefully it happens to all of us sometime.
Yep, I think we've all been there, done that. But... did this actually fix your problem? (I would assume so...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 2:06 pm
The CTE fixed it in live, but now we know that it just did it because of the order. I had reproduced the problem on stage and was trying to solve it this morning, and yes, as soon as I fixed the code it worked exactly like I was expecting it to work all along.
I don't know what I would do when stuck in a rut without a place like this. With so many of you folks around, SOMEONE is bound to find the groaner being overlooked. This place is awesome.
September 15, 2010 at 2:12 pm
And to be explicit, that last post was also intended as a thank-you to Wayne....
September 15, 2010 at 2:25 pm
You're quite welcome. Glad I could help.
jeff.mason (9/15/2010)
I don't know what I would do when stuck in a rut without a place like this. With so many of you folks around, SOMEONE is bound to find the groaner being overlooked. This place is awesome.
Quite true. Both about the place, and all of the folks that hang out here just helping others.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 2:50 pm
Steve posted a link a few months ago to a study that found out that working 40 hours / week for 2 months gave the same exact output as working 60 hours/week for the same 2 months. So that 160 EXTRA hours was for nothing on the long run.
When the body is overworked it does stupid mistakes and misses the obvious.
You now have the perfect proof. There's no way in hell 3 smart people would miss this at 10 AM on a thuesday morning after a good night's sleep.
September 15, 2010 at 5:46 pm
jeff.mason (9/15/2010)
Yeah, I know, I feel stupid too. What can I say, it was 11:30 PM, we are tired, and stuck in a rut for troubleshooting. We attacked the WHERE clause and did every form of INSERT we could think of, but no one noticed that the staging table and the destination table were in different column orders. Hopefully it happens to all of us sometime.
Heh... oh no... please don't feel stupid. We've ALL made a similar or worse mistake and I was just having a little fun because this mistake made me feel less alone in the world of making mistakes. 😉
Thanks for the very cool feedback, Jeff. I'm the same way... I love this site and I love the people on this site.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply