sql2008 merge statment

Question

hi all, i have a merge statement in sql2008 that looks like this:

  MERGE dbo.People t USING @People p ON t.ID = p.ID WHEN MATCHED AND (t.Name <> p.Name) THEN UPDATE SET t.Name = p.Name;

the problem exists when the target table has a null in it, the name doesnt get updated. i suppose i can wrap the p.name with isnull(p.name,'') for the comparison but i want to know if there is a sql server setting that can change the default handling of this occurance? similar to the way you can SET CONCAT_NULL_YIELDS_NULL ON to change the handling of concatenated nulls...

Answer 1

0

MERGE dbo.People t USING @People p ON t.ID = p.ID WHEN MATCHED AND (t.Name <> p.Name Or t.Name is NULL) THEN UPDATE SET t.Name = p.Name;

answered Oct 18 '10 at 08:44 PM

Cyborg
10.1k ● 29 ● 39 ● 44

i was trying to avoid changing the script (its a big script) hence i was asking if there is a db/trans level setting that can change the evaluation logic... if im going to use t.name <> p.name or t.name is null, than i may as well just use an t.name <> isnull(p.name, '')? or am i missing something? i guess your method is better when i dont no what to replace the null char with...

Oct 18 '10 at 09:25 PM DavidD

David if you are aware about the data inside table then, you can try SET ANSI_NULLS OFF inside your SP. But my suggestion is to change the code inside SP rather than changing ANSI Settings

Oct 18 '10 at 09:35 PM Cyborg

(comments are locked)

Answer 2

0

Remove AND (t.Name <> p.Name)

answered Oct 18 '10 at 06:12 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

i would have done that already if i always was doing an update regardless of whether the field value had changed. the actual query has more fields and lots of records. i dont want to trigger an update if the values havent changed. not an option

Oct 18 '10 at 07:19 PM DavidD

@DavidD Sorry, I did not read the question carefully. Better approach would be

WHEN MATCHED AND (t.Name  p.Name or t.Name is null)

Oct 18 '10 at 07:49 PM Oleg

(comments are locked)

sql2008 merge statment

Follow this question

Related Questions