Applying SQL UPDATE to Dataverse tables, How?

[Walad100100]

I come from the RDBMS world where I can update the data of any given table using the classic SQL UPDATE statement.  How to do so for any Dataverse Table? I spent weeks searching for a mechanism to do so and I failed. All I found is how to use Power BI and Power Query to extract information (including dataflow). 

I know that I can edit a Dataverse table using Excel directly, but I need an advanced mechanism such as the UPDATE statement that enables me to specifically select what rows to update.

Your help, please.

[GuidoPreite · Accepted solution]

You can't execute UPDATE SQL statements against Dataverse, only the READ is supported by the TDS endpoint.

There is a XrmToolBox tool called SQL 4 CDS that will convert and execute your SQL statement to the Dataverse API

[Walad100100]

@GuidoPreite Thank you for your prompt response and effective answer.

I am really puzzled:

1. Why such a fundamental need for modifying Dataverse tables is NOT provided natively by Microsoft especially that most developers are coming from the RDBMS world. 

2. In Microsoft's documentation, their focus is entirely on the READ-ONLY mode where we can use Power BI and SSMS, etc. which is not realistic for developing even a silly app. We will always need to mass-update data for one reason or another. 

3. Is there another mechanism other than SQL 4 CDS (which is not from Microsoft)?

4. This question was not raised in this forum before, which makes me question myself and whether I am following the best practices with Dataverse.

Advise, please. -- Mega Thanks! 

2. 

[GuidoPreite]

1. Because Dataverse comes from Dynamics CRM (the current model-driven apps) where there is the concept of supported and unsupported customizations. With supported customizations Microsoft guarantees model-driven apps work (because the Dataverse layer is how they intended) if they allow ALTER, UPDATE or DELETE sql statements there is an high possibility that model-driven apps stop to work, so they consider this unsupported customizations

2. "which is not realistic for developing even a silly app", if you are developing a canvas app you can use a SQL DB source directly, you don't need Dataverse, Dataverse comes with its own advantages (like the security layer and roles for accessing the data) that you will need to build in your SQL database. Canvas apps can use different data sources, model-driven apps are based on Dataverse.

3. No, there isn't another tool. Keep in mind that SQL 4 CDS converts your SQL statement to supported API calls for Dataverse, it does not execute SQL queries directly (except for the read operation if the TDS endpoint is enabled).

[Walad100100]

Highly informative indeed -- Thanks.

[soccer]

I am in full agreement: Microsoft: IMPLEMENT a way for developers to UPDATE a Dataverse table with something like an SQL UPDATE statement!!

[MajdYazigi]

You are totally right in your case, and it is really strange why not lot of people are asking the same question, I found the best solution for it is to link the dataverse table to MS Access then you can use the familiar SQL commands against the table, though the speed of execution is not much pleasing but it does the job...

[swinterrowd]

I realize I am late jumping in on this. Thats because, I am new to the Power Apps environment and used SSMS in the past. I tend to agree with some of the developer viewpoints expressed here. The methods to see and interact with the data in the Dataverse tables are clumsy. In addition, the methods to update multiple rows in a table are difficult. I thought Guido made some compelling comments that were enlightening to me. I have been using and I do like the model driven approach. However, to work around the short comings, I have been trying to use canvas app buttons to execute Update/Patch commands. They work. However, I am unable to get them working inside a ForAll loop. If someone can help me understand an easier way to accomplish the tasks discussed here, please share with me. In addition: does the "SQL 4 CDS" tool allow you to see the commands it generates after the conversion? That might be informative as the Microsoft documentation and examples seem to address only simple concepts.

[swinterrowd]

@GuidoPreite Thanks for your comments. I can appreciate the need to protect the applications within a production environment. However, while developing the application against a new set of tables in the Dataverse, there is a need for me to manipulate/update the data tables in bulk commands until you figure out how everything is working or to correct mistakes that are made during development. 

[PA_User]

Hello! I was working on the same problem so here is what I have for looping a patch. I created a collection so that I could use a checkbox to "Select" which items should be in the bulk patch. Default is set to false and on select of the checkbox the value in the temp table becomes true. Hope this helps! The collection is based on the actual db table and I have the rename column in there so that the column names aren't identical (Makes the patch more understandable). To give credit, I believe I got a lot of this from a Shane Young video. 

ForAll(
RenameColumns(
Filter(
SingleCustomerTable,
IsChosen
),
"ITEM_ID",
"PA_ITEM_ID"
),
Patch(
'TEST._ITEM_CATEGORIZATION',
LookUp(
'TEST._ITEM_CATEGORIZATION',
ITEM_ID = PA_ITEM_ID
),
{
L1: drp_LEVEL_1.Selected.LEVEL_TEXT,
L2: drp_LEVEL_2.Selected.LEVEL_TEXT,
L3: drp_LEVEL_3.Selected.LEVEL_TEXT,
CATEGORIZED_BY: User().Email,
CATEGORIZED_DATE: Now()
}
)
);