March 14, 2012 at 8:11 pm
SSIS SQL2k8r2
Ran into a strange problem with my Upsert package. A little background. I had to come up with a package to import flat files. The records will need to be inserted if they don't exist, or updated if they do. Did some research and found a relatively simple pattern for this problem. A lookup. The no match side inserts into the table, and the match side inserts into a view that essentially turns the insert into an update. Seems to be a classic pattern. It seemed to work well until I ran into this particular file today. The behavior is strange. This record is flowing down the no-match side and trying to insert, but as it turns out the key already exists so it fails. I don't understand how that's possible. If the key exists, it never should have came down the no match side right? How could this happen? Is it possible that the key was inserted from a previous record but the unique index was not updated in time?
Another thing worth pointing out is that I'm not using the primary key to do the upsert. I'm using a compound secondary key, and there is a unique index on the secondary key. Maybe this upsert strategy won't work with a non-primary key?
The error message gives me the key that it was trying to insert. If I manually insert a record with that key. It works perfectly. I'm baffled.
Maybe I'm just missing something real obvious. I'd be grateful for any ideas.
Thanks
.
March 15, 2012 at 2:04 am
Is that key an integer or string?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 15, 2012 at 9:42 am
It's a compound key, three strings.
.
March 15, 2012 at 11:34 am
Here's the trigger. Is this the right way to handle the inserts/updates? Notice that I'm finding the record to be updated via the joins. I omitted some of the fields for brevity.
CREATE TRIGGER trg_instead_insert_MemberLookup
ON dbo.vwMemberLookup
INSTEAD OF INSERT
AS
BEGIN
UPDATE MemberLookup
SET TradingPartner = i.TradingPartner
,DivisionId = i.DivisionId
,ServiceArea = i.ServiceArea
,GroupSFX = i.GroupSFX
,RecordType = i.RecordType
FROM dbo.MemberLookup
INNER JOIN inserted i ON i.SubscriberId = MemberLookup.SubscriberId
AND i.MemberId = MemberLookup.MemberId
AND i.GroupId = MemberLookup.GroupId
END
.
March 15, 2012 at 12:23 pm
I think I'm yanking everyone's chain for nothing. I think the problem is that the vendor told me the combination of these three keys is unique when in fact it is not.
I'm still concerned about the fact that the lookup says the row is NOT there, but then the insert fails because it IS there. That's still a mystery to me, but it's probably something I've done wrong, or don't understand about the data.
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply