April 22, 2009 at 5:40 pm
I've noticed that the MERGE function in SQL Server 2008 does not appear to handle NULLs when comparing two data sets. Take the following example:
--Create Stage table--
CREATE TABLE STAGE_TableA
(
TblKey INT NOT NULL,
Value1 nvarchar(15) NULL,
Value2 nvarchar(15) NULL
);
--Create target table--
CREATE TABLE TARGET_TableA
(
TblKey INT NOT NULL,
Value1 nvarchar(15) NULL,
Value2 nvarchar(15) NULL
);
--Insert records into table--
INSERT INTO STAGE_TableA VALUES(1, NULL, 'ABCDE')
INSERT INTO STAGE_TableA VALUES(2, 'XYZ', 'ABCDE')
INSERT INTO STAGE_TableA VALUES(3, 'XYS', 'ABCDE')
INSERT INTO STAGE_TableA VALUES(4, 'OPQ', 'ABCDE')
INSERT INTO TARGET_TableA VALUES(1, 'JHI', 'ABCDE')
INSERT INTO TARGET_TableA VALUES(2, 'XYZ', 'FGHIJ')
INSERT INTO TARGET_TableA VALUES(3, 'XYS', 'ABCDE')
INSERT INTO TARGET_TableA VALUES(4, 'IUO', 'ABCDE')
--check data sets--
SELECT * FROM STAGE_TableA
SELECT * FROM TARGET_TableA
--MERGE records using SQL Server 2008 MERGE functionality--
MERGE
INTO TARGET_TableA AS TGT
USING STAGE_TableA AS SRC ON TGT.TblKey = SRC.TblKey
WHEN MATCHED AND TGT.Value1 != SRC.Value1 THEN
UPDATE SET TGT.Value1 = SRC.Value1
OUTPUT
Inserted.TblKey,
'Value1' AS ColumnName,
Deleted.Value1 AS PriorValue,
Inserted.Value1 AS CurrentValue,
GETDATE() AS ChangeDate;
Notice that the only output is record #4 which MERGEs the value 'OPQ' into TARGET_TableA. Theoretically, because record #1 in the source table is NULL, then TARGET_TableA should also receive the value NULL. This does not occur however, which doesn't make much sense.
I am curious if anyone else has see this issue and has a way to handle it in the MERGE code?
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]
April 22, 2009 at 8:11 pm
Welcome to three value logic, which applies when a column may be null.
Under two value logic, the expression if ( a = b or a != b ) is always true but under three value logic the expression could be true or unknown, because if either variable is null, the expression will evaluate to unknown. Please also note that if both columns are null, then the expression evaluates to unknown.
Change the logic test to:
WHEN MATCHED
AND ( TGT.Value1 != SRC.Value1
( or ( TGT.Value1 is null and SRC.Value1 is not null )
or ( TGT.Value1 is NOT null and SRC.Value1 is null )
)
THEN
SQL = Scarcely Qualifies as a Language
April 23, 2009 at 10:44 am
Thanks!
I thought the solution might be something similar to that. I had the first OR statement in my test code, but not the second OR statement. I ran the code on the test tables and it seemed to work correctly.
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply