How to add multiple stopwords to a stopwords table ?

Question

Post reply

How to add multiple stopwords to a stopwords table ?

isuckatsql

SSChampion

Points: 10381
More actions
September 15, 2013 at 5:02 am

#277980

I keep getting this error message, when i use insert into or update.
insert into sys.fulltext_stopwords
values('5', 'resume', 'English', '1033')
I have 10k more words to add to the stopwords table, and i dont want to do it one at a time!
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
ALTER FULLTEXT STOPLIST stoplistian91413 ADD 'resume' LANGUAGE 'English';
I found the above command that works for one word. Is the only way to add 10k words by adding a loop?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

isuckatsql SSChampion Points: 10381 More actions · Answer 1

Would creating empty stoplist, then importing my 10k stopwords work ?

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

If you have all the words you want to add in a table, you can do

SELECT 'ALTER FULLTEXT STOPLIST MyStoplist ADD ' +

quotename(stopword, '''') + ' LANGUAGE ''English'''

FROM my_stopwords

Copy and execute the result set.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

isuckatsql SSChampion Points: 10381 More actions · Answer 3

Erland Sommarskog (9/15/2013)
If you have all the words you want to add in a table, you can do
SELECT 'ALTER FULLTEXT STOPLIST MyStoplist ADD ' +
quotename(stopword, '''') + ' LANGUAGE ''English'''
FROM my_stopwords
Copy and execute the result set.

Thanks!