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.
⚠ 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.
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;
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.
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!