PeterBaileyUk
asked on
dont insert unknown value into sql server table
I have a query that populates a word table but its allowing what looks like nulls or a word with unknown number of spaces and i am unsure how to deal with that.
use Dictionary
insert into TblCurrentWords (Word)
select Word
from TblWords
where word is not null and word <>' '
group by wordSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i got two responses at the same time and both work, how do i assign equal points? I know I had an issue last time i tried that where one got 1000 and the other expert nothing
ASKER
interestingly the latter didnt work it looked ok in a select but not when i inserted. i will try the first solution now
Mark the comments that you liked most (the one that you'll really use) as "Best Solution" and the other one as "Assisted Solution" and assign 250 points to each comment.
ASKER
one thing i left off as it didnt seem relevant was an additional and.
in any event both solutions permit an entry that looks null or has spaces.
in any event both solutions permit an entry that looks null or has spaces.
use Dictionary
insert into TblCurrentWords (Word)
select Word
from TblWords
where word is not null and RTRIM(LTRIM(word))<>'' and PATINDEX('%[0-9]%',Word)=0
group by wordASKER
I dont know what that empty first entry contains
Even in my opinion I would always avoid DISTINCT since it's a performance killer and the where word > '' doesn't really filter out the spaces.
in any event both solutions permit an entry that looks null or has spaces.Did you clean the table before running our suggestions? If so, please post a screenshot of the results.
ASKER
ASKER
i have to leave the empty words in the word table as they are linked to clientcodes and i need to identify them but in this example i am taking a snapshot of the total words in the table to store that.
The screenshots that I requested is the one after the INSERT command to check the cases that you talked about.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I lost internet connectivity in between, so by the time my comment was posted multiple ideas were already exchanged. Sorry for the confusion.
ASKER
ok Nakuls solution worked so i will share the points between the best two due to the speed and efficiency, is everyone ok with that? that means nakul and vitor.
ASKER
I know how to select the best and assisted solution but i am damned if i can see how to share the points. It causes great confusion.
Open in new window