If you are creating a GlobalSearch list with a small number of entries, it is easy to create the list using just the GlobalSearch client. If you are creating a list based on a SQL database, using an Assembly Bound list is often a viable solution as well. However, if you want to create a more permanent list with a large number of values that is not dynamically updated, or add a large number of values to an existing list, then doing a bulk import may be easier.
...
The steps in this article can cause irreversible damage to your database if followed improperly. Make sure you back up your SQL database prior to beginning. If you have any questions about your current SQL backup plan, please consult your database administrator or IT. For more information about backing up your GlobalSearch databases, please consult the following documentation:
Ensure the Import and Export Wizard is installed
The Import and Export Wizard is included with the SQL Server Data Tools (SSDT). Chances are, if you have SQL Server Management Studio, you have the Import and Export Wizard installed as well. If you need to download it, consult the following link:
Make sure you already have the list in GlobalSearch
...
For more information about creating GlobalSearch lists, please consult the following documentation:
Step 1: Get the list IDs for the lists you want to update.
...
Create a new CSV file using your text editor of choice (Notepad, Notepad++, Excel, etc.). Your CSV should have 2 columns, one for the ListID and one for the Value. The screenshot below shows an example which adds 3 new vendors to List ID 9 (as shown in step 1. This example uses column headers, but headers are not required. You can insert into multiple lists at a time, just make sure each list value is marked with the appropriate ID.
Perform the Insert
| Infowarning |
|---|
Do not proceed if you do not have a secure, reliable backup of your database. The following steps involve manipulating your SQL database directly and can cause irreversible damage if performed improperly. |
Open a command prompt. Type “dtswizard” and press Enter. This opens the Import and Export Wizard
On the next screen, select “Flat File Source” as your data source. Browse for your CSV file.
If it does not appear on the list of available files, make sure you have “All files (*.*)” selected for your file types.
If you are not using column headers in your CSV, make sure that “Column names in the first data row” is unchecked.
Click “Advanced” on the left. For your ListID column, make sure the DataType is set to “single-byte signed integer”. If your CSV file is set up properly, selecting “Suggest Types…” and clicking OK should do this for you.
Click Next and select your SQL instance, authentication method, and database.
Click Next. On the following screen, change the “Destination” to the table “[dbo].[ssListValues]”.
Click “Edit Mappings…”. Make sure that “Append rows to the destination table” is selected.
Do not select “Delete rows in destination table”. This will remove all items from all lists in GlobalSearch. The only way to fix this is restoring from a SQL backup.
Make sure that your CSV columns are mapped to the correct SQL columns.
If the columns are ordered (ListID,Value) in your CSV, then they will be mapped correctly by default.
Click OK, then Next. Review that everything is set up properly, then click Next again.
Click “Next”, then “Next”, then “Finish”.
If everything went successfully, the following window should look something like this.
Confirm in GlobalSearch that the new values have been added to your list. If the new values have not been added to the list, or any of your existing lists have been affected, restore your SQL database from a backup and start again.
...