How to use MERGE()

Question

Post reply

How to use MERGE()

rcagas-917601

Old Hand

Points: 378
More actions
March 29, 2009 at 12:50 am

#198608

Hello,
I have a target table with a unique index consisting of 6 fields. 2 of these fields are allowed Null values. I have a temporary table containing data that I need to either Update or Insert into the target table while maintaining the uniqueness. When using Insert with a standard inner join, I encountered problems when Null values were present in either of those 2 fields; although the records match, they were not being picked up. Will using MERGE avoid this problem with the Nulls? If so, what is the syntax? Thanks.
TargetTable:
Field1, Field2, Field3, Field4, Field5(allow Nulls), Field6(all Nulls), Field7.....
SourceTable:
Field1, Field2, Field3, Field4, Field5(allow Nulls), Field6(all Nulls), Field7.....

Viewing 9 posts - 1 through 8 (of 8 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

NULL is not a value and NULL is never equal to anything, even another NULL. Thus:

On NULL = NULL

will Never match.

To get around this, you will want to code the test for these two columns like this:

ON ( (Target.Field5 = Source.Field5)

OR (Target.Field5 IS NULL AND Source.Field5 IS NULL) )

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

rcagas-917601 Old Hand Points: 378 More actions · Answer 2

Thank you. It makes perfect sense now.

The MERGE statement that I came across seems to be quite useful. I tried to play around with it but came across a syntax error near "MERGE". Can you help?....

WITH

S AS (SELECT * FROM Source),

T AS (SELECT * FROM Target)

MERGE INTO T

USING S

ON (S.Field1 = T.Field1) AND

(S.Field2 = T.Field2) AND

(S.Field3 = T.Field2) AND

(S.Field4 = T.Field4) AND

((S.Field5 = T.Field5) OR (S.Field5 Is Null and T.Field5 Is Null)) AND

((S.Field6 = T.Field6) OR (S.Field6 Is Null and T.Field6 Is Null))

WHEN MATCHED THEN

UPDATE SET T.Field7 = S.Field7,

T.Field8 = S.Field8

WHEN NOT MATCHED THEN

INSERT ( Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8 )

VALUES ( S.Field1, S.Field2, S.Field3, S.Field4, S.Field5, S.Field6, S.Field7, S.Field8 )

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

MERGE was introduced in SQL Server 2008 - are you on that version?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

good catch Jeffrey!

On my 2005 server this SQL code gives me the same error as the OP, however on my 2008 server, I get "Invalid object name 'Source'." which is what you would expect since I do not have the tables.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]