March 10, 2014 at 9:02 am
My experience with MERGE statements is kind of limited so please bear with me. I'm trying to modify a MERGE statement where the UPDATE and INSERT pieces are working as expected but the "NOT MATCHED BY SOURCE"/DELETE is giving me problems.
Here's an example of the the code:
USE tempdb
GO
CREATE TABLE dbo.TestA (
SomeAId INT
, SomeSubAId INT
, SomeAValue CHAR(1)
, PRIMARY KEY CLUSTERED (SomeAId, SomeSubAId)
);
CREATE TABLE dbo.TestB (
SomeBId INT
, SomeSubBId INT
, SomeBValue CHAR(1)
, PRIMARY KEY CLUSTERED (SomeBId, SomeSubBId)
);
INSERT INTO dbo.TestA (SomeAId, SomeSubAId, SomeAValue) VALUES
(1, 1, 'A')
, (1, 2, 'B')
, (1, 3, 'C')
, (1, 4, 'D')
, (1, 5, 'E')
, (2, 1, 'A')
, (2, 2, 'B')
, (2, 3, 'C')
, (2, 4, 'D')
, (2, 5, 'E');
INSERT INTO dbo.TestB (SomeBId, SomeSubBId, SomeBValue) VALUES
(1, 1, 'L')
, (1, 2, 'M')
, (1, 3, 'N')
, (1, 4, 'O')
, (1, 5, 'P')
, (1, 6, 'Q')
, (1, 7, 'R');
--SELECTSomeAId = ISNULL(a.SomeAId, b.SomeBId)
--, SomeSubAId = ISNULL(a.SomeSubAId, b.SomeSubBId)
--, SomeAValue = ISNULL(b.SomeBValue, a.SomeAValue)
--FROMdbo.TestA a
--FULL OUTER JOIN dbo.TestB b
--ON a.SomeAId = b.SomeBId
--AND a.SomeSubAId = b.SomeSubBId;
MERGE dbo.TestA AS trgt
USING dbo.TestB AS src
ON trgt.SomeAId = src.SomeBId AND trgt.SomeSubAId = src.SomeSubBId
WHEN MATCHED THEN
UPDATE SETSomeAValue = src.SomeBValue
WHEN NOT MATCHED BY TARGET THEN
-- insert new records
INSERT (SomeAId, SomeSubAId, SomeAValue)
VALUES (src.SomeBId, src.SomeSubBId, src.SomeBValue)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SELECT * FROM dbo.TestA;
DROP TABLE dbo.TestA;
DROP TABLE dbo.TestB;
Here's what I'm expecting:
Here's what I get:
I'm thinking I need to play around with my joins/conditions but figured having another set of eyes look at it couldn't hurt.
_____________________________________________________________________
- Nate
March 10, 2014 at 11:27 am
Maybe I'm not understanding the problem, but to me it looks like you are not wanting to have anything happen with they are not matched by source. So don't add that to the merge statement:USE tempdb
GO
CREATE TABLE dbo.TestA (
SomeAId INT
, SomeSubAId INT
, SomeAValue CHAR(1)
, PRIMARY KEY CLUSTERED (SomeAId, SomeSubAId)
);
CREATE TABLE dbo.TestB (
SomeBId INT
, SomeSubBId INT
, SomeBValue CHAR(1)
, PRIMARY KEY CLUSTERED (SomeBId, SomeSubBId)
);
INSERT INTO dbo.TestA (SomeAId, SomeSubAId, SomeAValue) VALUES
(1, 1, 'A')
, (1, 2, 'B')
, (1, 3, 'C')
, (1, 4, 'D')
, (1, 5, 'E')
, (2, 1, 'A')
, (2, 2, 'B')
, (2, 3, 'C')
, (2, 4, 'D')
, (2, 5, 'E');
INSERT INTO dbo.TestB (SomeBId, SomeSubBId, SomeBValue) VALUES
(1, 1, 'L')
, (1, 2, 'M')
, (1, 3, 'N')
, (1, 4, 'O')
, (1, 5, 'P')
, (1, 6, 'Q')
, (1, 7, 'R');
SELECTSomeAId = ISNULL(a.SomeAId, b.SomeBId)
, SomeSubAId = ISNULL(a.SomeSubAId, b.SomeSubBId)
, SomeAValue = ISNULL(b.SomeBValue, a.SomeAValue)
FROMdbo.TestA a
FULL OUTER JOIN dbo.TestB b
ON a.SomeAId = b.SomeBId
AND a.SomeSubAId = b.SomeSubBId;
MERGE dbo.TestA AS trgt
USING dbo.TestB AS src
ON trgt.SomeAId = src.SomeBId AND trgt.SomeSubAId = src.SomeSubBId
WHEN MATCHED THEN
UPDATE SETSomeAValue = src.SomeBValue
WHEN NOT MATCHED BY TARGET THEN
-- insert new records
INSERT (SomeAId, SomeSubAId, SomeAValue)
VALUES (src.SomeBId, src.SomeSubBId, src.SomeBValue);
--WHEN NOT MATCHED BY SOURCE THEN
--DELETE;
SELECT * FROM dbo.TestA;
DROP TABLE dbo.TestA;
DROP TABLE dbo.TestB;
Does this work for you? It returns the expected outcome.
March 10, 2014 at 12:20 pm
Thanks for responding Keith and sorry I failed to include the DELETE case. My test scripts are evidently no better than my MERGE statements today.
The code (now w/ SomeBId 2 SomeSubBId 5 omitted from table dbo.TestB):
USE tempdb
GO
CREATE TABLE dbo.TestA (
SomeAId INT
, SomeSubAId INT
, SomeAValue CHAR(1)
, PRIMARY KEY CLUSTERED (SomeAId, SomeSubAId)
);
CREATE TABLE dbo.TestB (
SomeBId INT
, SomeSubBId INT
, SomeBValue CHAR(1)
, PRIMARY KEY CLUSTERED (SomeBId, SomeSubBId)
);
INSERT INTO dbo.TestA (SomeAId, SomeSubAId, SomeAValue) VALUES
(1, 1, 'A')
, (1, 2, 'B')
, (1, 3, 'C')
, (1, 4, 'D')
, (1, 5, 'E')
, (2, 1, 'A')
, (2, 2, 'B')
, (2, 3, 'C')
, (2, 4, 'D')
, (2, 5, 'E');
INSERT INTO dbo.TestB (SomeBId, SomeSubBId, SomeBValue) VALUES
(1, 1, 'L')
, (1, 2, 'M')
, (1, 3, 'N')
, (1, 4, 'O')
--, (1, 5, 'P')
, (1, 6, 'Q')
, (1, 7, 'R');
MERGE dbo.TestA AS trgt
USING dbo.TestB AS src
ON trgt.SomeAId = src.SomeBId AND trgt.SomeSubAId = src.SomeSubBId
WHEN MATCHED THEN
UPDATE SETSomeAValue = src.SomeBValue
WHEN NOT MATCHED BY TARGET THEN
-- insert new records
INSERT (SomeAId, SomeSubAId, SomeAValue)
VALUES (src.SomeBId, src.SomeSubBId, src.SomeBValue)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SELECT * FROM dbo.TestA;
DROP TABLE dbo.TestA;
DROP TABLE dbo.TestB;
The data I'm starting with in table dbo.TestA:
The result I'm expecting in table dbo.TestA:
The result I'm getting in table dbo.TestA:
In this scenario I only want to modify records related to SomeAId = 1.
I want to update SomeSubAId records 1, 2, 3, 4.
I want to insert SomeSubAId records 6, 7.
I want to delete SomeSubAId record 5.
Pretty straightforward as a 3 step operation (within a transaction) but I'm curious to see if I can apply this as a single MERGE statement.
_____________________________________________________________________
- Nate
March 10, 2014 at 1:34 pm
I don't have much experience with MERGE either, but this might help you.
MERGE dbo.TestA AS trgt
USING dbo.TestB AS src
ON trgt.SomeAId = src.SomeBId AND trgt.SomeSubAId = src.SomeSubBId
WHEN MATCHED THEN
UPDATE SETSomeAValue = src.SomeBValue
WHEN NOT MATCHED BY TARGET THEN
-- insert new records
INSERT (SomeAId, SomeSubAId, SomeAValue)
VALUES (src.SomeBId, src.SomeSubBId, src.SomeBValue)
WHEN NOT MATCHED BY SOURCE AND trgt.SomeAId /*= 1*/IN( SELECT SomeBId FROM dbo.TestB)THEN
DELETE;
March 10, 2014 at 1:43 pm
Thanks Luis! That seems to solve my issue!
_____________________________________________________________________
- Nate
March 10, 2014 at 1:49 pm
You're welcome. It's great to help, but I wonder if it's the best solution.
March 10, 2014 at 5:49 pm
Fair enough but it did get me past my mental block. Plus I still have to implement it then go through unit and load testing before I'd ever release it into the wild.
_____________________________________________________________________
- Nate
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply