Efficient Bulk Dataverse Updates with SQL

If you need to make bulk updates to your Dataverse or Dynamics 365 data, maybe to fix data quality problems or restructure your data, you may be exporting your data to Excel, making your changes and re-importing it. Or you may be considering setting up a more complex data pipeline in Azure Data Factory. I’d like to offer an alternative solution that you can use for quick one-off or regularly scheduled changes – SQL.

Getting set up

To run any of these commands you’ll need SQL 4 CDS installed. For some you’ll also need the TDS endpoint enabled. You can add SQL 4 CDS to either XrmToolBox or SQL Server Management Studio (SSMS).

⚠ Important: Do not run any UPDATE commands directly on an on-premise SQL Server – this is unsupported and will likely cause errors in the future. Running updates via SQL 4 CDS is safe however, as the updates are performed through the supported SDK methods.

Reassigning records

A salesperson leaves, or you restructure the team to split into vertical specialisms, and you need to reassign your account records. With SQL you can do this in bulk in a single command, e.g.

UPDATE account
SET    owneridtype = 'systemuser',
       ownerid     = CASE industrycodename 
                          WHEN 'Accounting' 
                            THEN 'fc96613f-12ae-4a8b-9db8-2cda6541622a' 
                          WHEN 'Engineering'
                            THEN 'b8aabcdc-f0d1-4b59-b37a-699c8abeb2ca' 
                          ELSE '9d78d47c-96bc-4af2-b1aa-6ae116a6479b' 
                     END
WHERE  statecode = 0;

This will update all active accounts (the statecode = 0 part) and change the ownerid depending on the industry. This ensures each account is passed to the right salesperson based on their specialisms. You can then either re-run this on a regular basis or use a Flow to keep it up to date on an on-going basis.

Note that, as well as setting the ownerid field, you also need to set the owneridtype field. This is because the ownerid field is a “polymorphic lookup” and so you need to tell it what type of record the ownerid guid relates to.

In the XrmToolBox version you’ll need to use slightly different syntax to set polymorphic lookup fields:

UPDATE account
SET    ownerid = CREATELOOKUP(
                   'systemuser', 
                   CASE industrycodename 
                        WHEN 'Accounting'
                          THEN 'fc96613f-12ae-4a8b-9db8-2cda6541622a' 
                        WHEN 'Engineering' 
                          THEN 'b8aabcdc-f0d1-4b59-b37a-699c8abeb2ca' 
                        ELSE '9d78d47c-96bc-4af2-b1aa-6ae116a6479b' 
                   END
                 )
WHERE  statecode = 0;

Updating primary contact

Over time the list of contacts in an account can change. Contacts leave and are deactivated, others may be promoted and become more important in your relationship. You can use a query like this to pick the best contact for each account. This version simply picks the earliest-created contact to be the primary:

UPDATE account
SET    primarycontactid = pc.contactid
FROM   account CROSS APPLY (SELECT   TOP 1 *
                            FROM     contact
                            WHERE    parentcustomerid = account.accountid
                                     AND statecode = 0
                            ORDER BY createdonutc ASC) AS pc
WHERE  (account.primarycontactid IS NULL
        AND pc.contactid IS NOT NULL)
       OR (account.primarycontactid IS NOT NULL
           AND pc.contactid IS NULL)
       OR (account.primarycontactid <> pc.contactid);

You could also use a more complex sort order to prioritise contacts based on job title or the number of activities:

UPDATE account
SET    primarycontactid = pc.contactid
FROM   account CROSS APPLY (SELECT   TOP 1 contactid
                            FROM     contact
                                     LEFT OUTER JOIN
                                     activityparty AS ap
                                     ON contact.contactid = ap.partyid
                            WHERE    contact.parentcustomerid = account.accountid
                                     AND contact.statecode = 0
                            GROUP BY contact.contactid
                            ORDER BY COUNT(*) DESC) AS pc
WHERE  (account.primarycontactid IS NULL
        AND pc.contactid IS NOT NULL)
       OR (account.primarycontactid IS NOT NULL
           AND pc.contactid IS NULL)
       OR (account.primarycontactid <> pc.contactid);

You can tweak the ORDER BY clause within the CROSS APPLY to ensure whatever you consider is the “best” contact comes to the top of the list.

You’ll need to have the TDS endpoint enabled for these queries to work. Once it’s enabled you can run these queries from either SQL 4 CDS in XrmToolBox or SSMS.

Migrating option sets to many-to-many relationships

A common way of restructuring data is to move from a simple optionset field to something that can hold multiple options, either a many-to-many relationship or a multi-select picklist. It’s easy enough to create the new field, but how do you migrate the data? SQL to the rescue again:

INSERT INTO new_manytomany (contactid, new_jobroleid)
SELECT contactid,
       CASE new_jobtitle 
            WHEN 1 THEN 'efd17e43-84f8-4c4b-8fae-320d16dd5b7c' 
            WHEN 2 THEN '140800f0-c53f-4611-af17-1aef82e99487' 
       END
WHERE  new_jobtitle IS NOT NULL;

Just extend the mappings in the highlighted lines to convert the optionset values to the guids of the new related entities and you can convert all your records in one query!

If you create a multi select picklist field with the same options as your existing single picklist field you can do the even easier

UPDATE contact
SET    new_multijobtitle = new_jobtitle
WHERE  new_jobtitle IS NOT NULL;

Now you’ve migrated your existing optionset data, your users can concentrate on adding the extra values without having to re-select the data they’d already put into the old field.

I hope you find this useful, please let me know in the comments any other patterns you find useful!