May 24, 2013 at 3:13 am
OK,
need a fast way to compare new data coming in to old data. Both are in seperate tables so we went for Merge. In testing I noticed that the comparison does not seem to be working. The columns are a selection of different data types so dates, chars, and numbers.
Is there an easy way of doing this quickly and ensuring they are checked for change?
Cheers
E
MERGE TargetTable AS TARGET
USING
(SELECT
Target_ID
, Column1
, Column2
, Column3
, Column4
FROM
SourceTable
) AS SOURCE
ON (TARGET.Target_ID = SOURCE.Target_ID )
------------------------------------------------------------------------
--WHEN MATCH ON KEY COLUMN AND THERE IS A CHANGE TO DATA
------------------------------------------------------------------------
WHEN MATCHED AND
(
TARGET.Column1 <> SOURCE.Column1 OR
TARGET.Column2 <>SOURCE.Column2 OR
TARGET.Column3 <>SOURCE.Column3 OR
TARGET.Column4 <>SOURCE.Column4
)THEN
UPDATESET
TARGET.Column1 = SOURCE.Column1,
TARGET.Column2 = SOURCE.Column2,
TARGET.Column3 = SOURCE.Column3,
TARGET.Column4 = SOURCE.Column4
-----------------------------------
-- WHEN NO MATCH, INSERT NEW RECORD
-----------------------------------
WHEN NOT MATCHED BY TARGET THEN
INSERT (Target_ID, Column1, Column2, Column3, Column4)
VALUES(SOURCE.Target_ID,SOURCE.Column1,SOURCE.Column2,SOURCE.Column3,SOURCE.Column4)
------------------------------------------
-- WHEN IN TARGET BUT NOT IN SOURCE DELETE
------------------------------------------
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $Action
May 24, 2013 at 3:27 am
Do you mean your comparison of TARGET.Column1 <> SOURCE.Column1 is not working when one of this is NULL?
There are few ways to deal with it. Here is one, I guess more preferable as it does not depend on datatype:
WHERE
CASE WHEN TARGET.Column1 = SOURCE.Column1 OR TARGET.Column1 IS NULL AND SOURCE.Column1 IS NULL THEN 1 ELSE 0 END= 0
OR
CASE WHEN TARGET.Column2 = SOURCE.Column2 OR TARGET.Column2 IS NULL AND SOURCE.Column2 IS NULL THEN 1 ELSE 0 END= 0
...
May 24, 2013 at 3:33 am
Eugene Elutin (5/24/2013)
WHERE
CASE WHEN TARGET.Column1 = SOURCE.Column1 OR TARGET.Column1 IS NULL AND SOURCE.Column1 IS NULL THEN 1 ELSE 0 END= 0
OR
CASE WHEN TARGET.Column2 = SOURCE.Column2 OR TARGET.Column2 IS NULL AND SOURCE.Column2 IS NULL THEN 1 ELSE 0 END= 0
...
Why not use ISNULL FUNCTION INSTEAD
CASE WHEN ISNULL(TARGET.Column1,0) = ISNULL(SOURCE.Column1,0) THEN 1 ELSE 0 END
May 24, 2013 at 3:39 am
Had looked at ISNULL and it seemed the obvious easy choice. However from my understanding there are two pitfalls.
1. If you choose a valid replcaement for null such as Zero if its an integer then if the new target is NULL and the source is Zero then we get a match even though they were not matched.
2 Then I consider an invalid value say 'BANANA' if the integer is NULL in the target but then when checking against a real integer you would get an error.
There must be a simple way to do this although cant seem to find it.
WHERE
CASE WHEN TARGET.Column1 = SOURCE.Column1 OR TARGET.Column1 IS NULL AND SOURCE.Column1 IS NULL THEN 1 ELSE 0 END= 0
OR
CASE WHEN TARGET.Column2 = SOURCE.Column2 OR TARGET.Column2 IS NULL AND SOURCE.Column2 IS NULL THEN 1 ELSE 0 END= 0
...
Thanks, I had considered that but hoped there would be a cleaner simpler less code solution. Maybe I just want the moon on a stick 😀
Cheers
E
May 24, 2013 at 3:45 am
Ells (5/24/2013)
2 Then I consider an invalid value say 'BANANA' if the integer is NULL in the target but then when checking against a real integer you would get an error.E
Home come the DataType of your Target Table and Source Table can be differ and if anytime it is differ too, how can you compare it ?
May 24, 2013 at 3:46 am
...
Why not use ISNULL FUNCTION INSTEAD
CASE WHEN ISNULL(TARGET.Column1,0) = ISNULL(SOURCE.Column1,0) THEN 1 ELSE 0 END
Because of two things:
1. It will need to be different based on datatype (numeric, datetime, character's)
2. It will see no difference between NULL and the value used in function
May 24, 2013 at 3:50 am
...
Thanks, I had considered that but hoped there would be a cleaner simpler less code solution. Maybe I just want the moon on a stick 😀
Cheers
E
You can wrap into:
WHERE ISNULL(NULLIF(dst.COL1,src.COL1),NULLIF(src.COL1,dst.COL1)) IS NOT NULL
OR ISNULL(NULLIF(dst.COL2,src.COL2),NULLIF(src.COL2,dst.COL2)) IS NOT NULL
...
Or like that
WHERE NULLIF(dst.COL1,src.COL1) IS NOT NULL
OR NULLIF(src.COL1,dst.COL1) IS NOT NULL
OR NULLIF(dst.COL2,src.COL2) IS NOT NULL
OR NULLIF(src.COL2,dst.COL2) IS NOT NULL
...
May 24, 2013 at 4:23 am
Eugene Elutin (5/24/2013)
...
Thanks, I had considered that but hoped there would be a cleaner simpler less code solution. Maybe I just want the moon on a stick 😀
Cheers
E
You can wrap into:
WHERE ISNULL(NULLIF(dst.COL1,src.COL1),NULLIF(src.COL1,dst.COL1)) IS NOT NULL
OR ISNULL(NULLIF(dst.COL2,src.COL2),NULLIF(src.COL2,dst.COL2)) IS NOT NULL
...
Or like that
WHERE NULLIF(dst.COL1,src.COL1) IS NOT NULL
OR NULLIF(src.COL1,dst.COL1) IS NOT NULL
OR NULLIF(dst.COL2,src.COL2) IS NOT NULL
OR NULLIF(src.COL2,dst.COL2) IS NOT NULL
...
Thanks. Funny that I had been looking at NULLIF literally 30 minutes ago but did not visualise that it could be done that way.
NULLIF seems the neatest answer so far.
Cheers
E
😀
May 24, 2013 at 4:28 am
Just remember that NULLIF is not really a native function (like ISNULL), and it's compiled into CASE WHEN ...
Saying above, it's true that it does look neater.
May 24, 2013 at 4:39 am
Just double checked and looks like a suitable option.
Cheers
E
May 24, 2013 at 4:46 am
select
CASE WHEN NULLIF('B',NULL) IS NOT NULL THEN 'NO MATCH' ELSE 'MATCH' end NoMatch1,
CASE WHEN NULLIF(CAST(NULL AS VARCHAR(20)),NULL)IS NOT NULL THEN 'NO MATCH' ELSE 'MATCH' end Match2,
CASE WHEN NULLIF(CAST(NULL AS VARCHAR(20)),'B')IS NOT NULL THEN 'NO MATCH' ELSE 'MATCH' end NoMatch3,
CASE WHEN NULLIF('BANANA','BANANA')IS NOT NULL THEN 'NO MATCH' ELSE 'MATCH' end Match4
And Results in are
NO MATCH (OK)
MATCH (OK)
MATCH (Whoops)
MATCH (OK)
So alls not wellunless I made a mistake or missed something?
May 24, 2013 at 5:41 am
That is not the same as I've shown to you.
If you use NULLIF it's crucial to compare values twice:
NULLIF(d.COL1, src.COL1) IS NOT NULL OR NULLIF(src.COL1, d.COL1)
So your checking code should look like:
;WITH vals
AS
( SELECT 'B' V1, NULL V2
UNION ALL SELECT NULL V1, NULL V2
UNION ALL SELECT NULL V1, 'B' V2
UNION ALL SELECT 'BANANA' V1, 'BANANA' V2)
SELECT *
,CASE WHEN NULLIF(V1,V2) IS NOT NULL OR NULLIF(V2,V1) IS NOT NULL THEN 'NO MATCH' ELSE 'MATCH' END AS Match
FROM vals
May 24, 2013 at 6:22 am
Checked and works.
You would have thought tha MS would consider that there should be an additional option to the normal SQL comparison operators to allow to compare against a NULL.
Would also get rid of a lot of accidental code errors.
Many thanks for the solution.
E
🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply