Trouble with a MERGE statement

Question

Post reply

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'); --SELECT SomeAId = ISNULL(a.SomeAId, b.SomeBId) -- , SomeSubAId = ISNULL(a.SomeSubAId, b.SomeSubBId) -- , SomeAValue = ISNULL(b.SomeBValue, a.SomeAValue) --FROM dbo.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 SET SomeAValue = 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: 9888 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');

SELECT	SomeAId = ISNULL(a.SomeAId, b.SomeBId)

		, SomeSubAId = ISNULL(a.SomeSubAId, b.SomeSubBId)

		, SomeAValue = ISNULL(b.SomeBValue, a.SomeAValue)

FROM	dbo.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 SET	SomeAValue = 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 SET	SomeAValue = 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: 183686 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 SET	SomeAValue = 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

RP_DBA

SSCertifiable

Points: 5116

March 10, 2014 at 1:43 pm

#1695730

Thanks Luis! That seems to solve my issue!

_____________________________________________________________________
- Nate

@nate_hughes

Luis Cazares SSC Guru Points: 183686 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

RP_DBA

SSCertifiable

Points: 5116

March 10, 2014 at 5:49 pm

#1695791

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