How to Set a Primary ID in SQL Datasets

Background information

If your SQL dataset is not optimized, it may not be as efficient as the API dataset. We recommend using the API dataset whenever possible for optimal performance. You may receive the following pop up prompt.

If you still want to use the SQL dataset, you can set a primary id in the SQL dataset, so MSPbots can identify the uniqueness of data based on this primary id. Then, according to the update frequency you set, MSPbots will timely locate and update only the changed data, rather than deleting all data and resynchronizing, as there is no data in the dataset during this time.

This approach can save a considerable amount of time and resources, while reducing the complexity of data processing. By updating only the changed data on a scheduled basis, you can maintain the data's latest state, ensuring the consistency and accuracy of the system's data.

How to set a primary id

1. Log in to MSPbots as an admin.
2. Go to Datasets.
3. Search for the dataset you want to edit.
4. Click    > Edit.
   
   
   
   
5. Click Data Cleaning.
   
   
   
   
6. Fill in the following fields:
   
   • Select a database as the DB, for example, sqlagent.
   • Input SQL commands that can be used to query the data to be set as the ID.
   • Enter the appropriate frequency for syncing data in Sync Frequency (Mins).
     It is generally recommended to set it to at least 30 minutes.
     
     
     
     
7. Click Confirm.
8. Click the Set As Id button in the row where you want to set the primary id field.
   
      *   Only fields with unique data values can be set as primary id.
      *   This is a mandatory configuration. If you do not set it, you will receive a prompt.
   
          
   
   
   
   
   
   
9. When the Note pop up appears, click Confirm.
   
   
   
   
10. Once the setup is completed, a label will appear in front of the primary id field.
    • If you want to change the primary id field, click Remove As Id, and then reset a primary id.
      
      
      
      
11. Click OK to keep your settings.
12. Data synchronization takes some time. Please wait for a while, then refresh or reopen the dataset.