Merge in SQL 2008+

[guest2013-1]

i have the following:

MERGE datawarehouse_table AS TARGET
USING live_table AS SOURCE
	ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED
	AND TARGET.STATUS <> SOURCE.STATUS
	THEN
		UPDATE.....

The above updates a record that has a different status to the original (it updates all fields). However, sometimes a record is saved with a comment, which doesn't necessarily mean that the status changed.

I want to do this, but it doesn't seem to update the differences:

MERGE datawarehouse_table AS TARGET
USING live_table AS SOURCE
	ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED
	AND TARGET.STATUS <> SOURCE.STATUS OR TARGET.Comment_1 <> SOURCE.Comment_1 OR TARGET.Comment_2 <> SOURCE.Comment_2 OR  TARGET.Comment_3 <> SOURCE.Comment_3 OR  TARGET.Comment_4 <> SOURCE.Comment_4
	THEN
		UPDATE.....

Any ideas why it's not working?


(example I was working from: http://www.mssqltips.com/sqlservert...to-insert-update-and-delete-at-the-same-time/)

 

[LPCPT]

My 2c
Put all the "OR" conditions in brackets.

 

[diabolus]

Not seeing anything strange. Is the program going into the update statement? (i.e. is it finding mismatches?) Put a "print" statement in there, to see whether it is actually picking up any differences?

Maybe look into your nulls? (assuming you have any) I usually get these issues when i'm trying to compare a null value to a non-null value. Usually have to either check specifically "TARGET.Status is not null" or do an ISNULL(Target.Status,'') type of thing. Nulls can break all kinds of obvious things.

 

[guest2013-1]

No nulls
Already tried the brackets, that's why I posted here initially
OUTPUT $action,inserted*,deleted.* doesn't return the difference

 

[xrapidx]

how are you calling/executing the merge statement? in a trigger/view, ssis , stored procedure, etc.

 

[guest2013-1]

just normal sql statement in query analyzer

 

[xrapidx]

Hmmm...well it looks right, you sure its not a problem with your UPDATE section?

 

[guest2013-1]

No idea, I just followed the article. Everything works well until I try to check more than 1 column. Then I get constant updates on the same rows even though the source and target column data is exactly the same. Somehow with the OR and more than 1 column check, it spazzes out

 

[xrapidx]

How are you inserting the data?

 

[guest2013-1]

with the merge statement as well.

I did an initial

insert into target
select * from source

to get it loaded and synced

then I do the merge:

merge target using source when matched and target.column <> source.column then update set target.column = source.column
when not matched by target insert into
when not matched by source delete

 

[xrapidx]

Thats odd then.... probably going to be something really simple - I've done that once or twice with MERGE statements, wasting hours of my time.