x

sql2008 merge statment

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...

more ▼

asked Oct 18, 2010 at 05:23 PM in Default

avatar image

DavidD
79 5 5 10

The setting is set ansi_nulls off; This is a very evil setting which completely disregards the ANSI logic, which states that a comparison of any value with null is neither true nor it is false, but rather unknown. Setting the ansi_nulls to off allows chicanery like null = null evaluating to true.

Oct 18, 2010 at 07:46 PM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first


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;

more ▼

answered Oct 18, 2010 at 08:44 PM

avatar image

Cyborg
10.8k 37 54 51

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, 2010 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, 2010 at 09:35 PM Cyborg
(comments are locked)
10|1200 characters needed characters left

Remove AND (t.Name p.Name)

more ▼

answered Oct 18, 2010 at 06:12 PM

avatar image

Scot Hauder
6.4k 13 16 22

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, 2010 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, 2010 at 07:49 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2079
x53
x37

asked: Oct 18, 2010 at 05:23 PM

Seen: 1570 times

Last Updated: Oct 19, 2010 at 12:50 AM

Copyright 2016 Redgate Software. Privacy Policy