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

DavidD gravatar image

DavidD
79 5 5 7

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

Cyborg gravatar image

Cyborg
10.6k 36 40 45

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

Scot Hauder gravatar image

Scot Hauder
6k 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, 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1834
x47
x30

asked: Oct 18, 2010 at 05:23 PM

Seen: 1371 times

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