Trouble with a MERGE statement

Question

Trouble with a MERGE statement

RP_DBA

SSCertifiable

Points: 5116
More actions
March 10, 2014 at 9:02 am

#396394

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
@nate_hughes

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

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.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

RP_DBA SSCertifiable Points: 5116 More actions · Answer 2

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

@nate_hughes

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RP_DBA SSCertifiable Points: 5116 More actions · Answer 4

Thanks Luis! That seems to solve my issue!

_____________________________________________________________________
- Nate

@nate_hughes

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You're welcome. It's great to help, but I wonder if it's the best solution.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RP_DBA SSCertifiable Points: 5116 More actions · Answer 6

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

@nate_hughes