July 28, 2014 at 12:55 pm
I have the following 2 tables:
create table #Grantors
(
EntryNumber int,
Sequence int,
NameRIN int
)
Insert #Grantors (EntryNumber, Sequence, NameRIN)
Values
(473572,1,184991)
,(473572,2,184992)
,(473572,3,244602)
,(473573,1,57179)
,(473573,2,244603)
,(473574,1,184991)
,(473574,2,244604)
create table #tempTors
(
EntryNumber int,
Sequence int,
NameRIN int
)
Insert #tempTors (EntryNumber, Sequence, NameRIN)
Values (473574, 1,184991)
I am trying to merge the #tempTors table into the #Grantors table. I have been successful except when the "NOT MATCHED BY SOURCE" executes! It deletes every row in the destination table except the 1 in row from the source. I understand the logic that is being used, but I don't like it!
How do I modify my merge statement to only delete the one row so I get the following output? (Just delete the entrynumber 473574 seqence 2)
EntryNumberSequenceNameRIN
473572 1 184991
473572 2 184992
473572 3 244602
473573 1 57179
473573 2 244603
473574 1 184991
My current Merge statement:
MERGE #Grantors as Target
USING #tempTors as Source
ON (Target.EntryNumber = Source.EntryNumber and Target.Sequence = Source.Sequence)
--If the entrynumber and the sequence is the same, only update if the NameRIN changed
WHEN MATCHED AND Target.NameRIN <> Source.NameRIN THEN
UPDATE SET
Target.NameRIN = Source.NameRIN
--When there is a row in the tempTable, but not in Grantors, Insert it
WHEN NOT MATCHED BY Target THEN
INSERT (EntryNumber, Sequence, NameRIN)
VALUES (Source.EntryNumber, Source.Sequence, Source.NameRIN)
--When there is a row in Grantors but not in the tempTable, delete the Grantors row
WHEN NOT MATCHED BY Source THEN
DELETE;
Thanks!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 28, 2014 at 1:21 pm
AFAIK, you need an additional step as the condition differs for the "NOT MATCHED BY Source".
You could do it like this:
MERGE #Grantors as Target
USING #tempTors as Source
ON (Target.EntryNumber = Source.EntryNumber and Target.Sequence = Source.Sequence)
--If the entrynumber and the sequence is the same, only update if the NameRIN changed
WHEN MATCHED AND Target.NameRIN <> Source.NameRIN THEN
UPDATE SET
Target.NameRIN = Source.NameRIN
--When there is a row in the tempTable, but not in Grantors, Insert it
WHEN NOT MATCHED BY Target THEN
INSERT (EntryNumber, Sequence, NameRIN)
VALUES (Source.EntryNumber, Source.Sequence, Source.NameRIN);
--When there is a row in Grantors but not in the tempTable, delete the Grantors row
--WHEN NOT MATCHED BY Source THEN
-- DELETE;
DELETE g
FROM #Grantors g
WHERE EXISTS( SELECT *
FROM #tempTors t
WHERE g.EntryNumber = t.EntryNumber
AND g.Sequence <> t.Sequence)
You can add an explicit transaction to ensure atomicity.
July 28, 2014 at 3:56 pm
Luis Cazares (7/28/2014)
--When there is a row in Grantors but not in the tempTable, delete the Grantors rowWHEN NOT MATCHED BY Source AND Target.EntryNumber = @PassedInEntryNumber THEN
DELETE;
DELETE g
FROM #Grantors g
WHERE EXISTS( SELECT *
FROM #tempTors t
WHERE g.EntryNumber = t.EntryNumber
AND g.Sequence <> t.Sequence)
Your suggestion got me thinking about the problem a bit more. When I looked at your comment about needing additional criteria, it dawned on me that this will only execute for 1 entry number that is in the temp table. (Data entry stuff). By adding the @PassedInEntryNumber to the NOT MATCHED BY Source CLAUSE it was able to add that extra dimension to do the delete properly.
Thanks for the help.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply