August 18, 2008 at 8:24 am
Hi guys
Got another weird but hopefully simple problem...
INSERT INTO tblType
SELECT DISTINCT [Type] from #tblUploadA
WHERE [Type] not in
(SELECT Description
FROM tblType)
What's supposed to happen is that I insert into tblType any [Type] which is present in #tblUploadA but does not already exist in tblType.
When I run SELECT DISTINCT [Type] from #tblUploadA
I get the values 'Type1', 'Type2', and NULL returned
When I run SELECT Description FROM tblType
I get NULL returned
So by my interpretation, this should insert values 'Type1' and 'Type2' into tblType.
But for some reason it doesn't.
And I'm guessing that reason is NULL values.
When I try running
SELECT DISTINCT [Type] from #tblUploadA
WHERE [Type] not in
(SELECT Description
FROM tblType)
Nothing is returned.
Any ideas?
TIA 🙂
August 18, 2008 at 8:26 am
ok, solved it myself
INSERT INTO tblType
SELECT DISTINCT [Type] from #tblUploadA
WHERE [Type] not in
(SELECT Description
FROM tblType
WHERE Description IS NOT NULL)
easy 😀
Still not sure why it didn't work originally though
August 18, 2008 at 9:27 am
"Where Not In" returns all values that aren't contained in the set. Since null is an unknown value, it could be equal to anything, so Where Not In won't return any rows at all if one of the values in it is null.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2008 at 9:44 am
Thanks again Gsquared...
Your answer makes sense but has me slightly concerned..
What I'm trying to do is basically when a dataset is imported into a "staging" table, then normalise it by splitting the data out into various lookup tables.
So for Type, if the dataset has typeA, typeB, and NULL in the original dataset, I will first check for any missing Types in my Type table and add those, then go back and update the staging table to replace typeA with 1, typeB with 2, NULL with 3 and so on...
If I use my solution, ie add them to the type table where not NULL, then I won't have NULL in my Type table... this might cause problems with the "normalised" data.... hmmmm... guess I'll need to look into this further and do some testing myself.
thanks again though 🙂
August 19, 2008 at 8:08 pm
If you want to include nulls, but make sure they don't exist in the target table, you'll have to do two tests.
Do your "Where Not In" test, then also add:
or Col is null
and not exists
(select *
from TargetTable
where Col is null)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply