How to add a table of words to stopwords in SQL Server

Question

I have a table of words that want to add to stopwords in SQL Server. How can I do it?

Am I should add them one by one?

I try by:

insert into sys.fulltext_stopwords (stopword) select stopword from Table_1

but get this error:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

I use SQL Server 2022.

Answer 1

try:

-- Add stopwords to the custom stoplist
DECLARE @stopword NVARCHAR(64);
DECLARE @sql NVARCHAR(MAX);

DECLARE stopword_cursor CURSOR FOR
SELECT stopword FROM Table_1;

OPEN stopword_cursor;

FETCH NEXT FROM stopword_cursor INTO @stopword;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the dynamic SQL statement
    SET @sql = N'ALTER FULLTEXT STOPLIST CustomStoplist ADD ''' + @stopword + N''' LANGUAGE 0;';
    
    -- Execute the dynamic SQL statement
    EXEC sp_executesql @sql;
    
    FETCH NEXT FROM stopword_cursor INTO @stopword;
END

CLOSE stopword_cursor;
DEALLOCATE stopword_cursor;

• make sure that Table_1 exists and contains the stopwords you want to add
• make sure to replace YourTableName, YourColumnName, and YourPrimaryKeyIndex with the actual names of your table, column, and primary key index.