Power Automate - Insert/ Updates rows in On-prem SQL server table

Question

My goal is to copy data from one SQL table to another SQL Table.

I'm trying to create a conditional Insert/Update statement in Power Automate based on Row ID. I have two tables right now with the same columns.

1. Source SQL Table

2. Destination SQL Table

I would like to update rows if Row ID already exists or create new if already not exists.

I tried Execute SQL query but this is not supported.(Known issues)

I tried "Transform data using Power Query to fetch rows from Source and Destination" and then had if condition to compare "Source.ProjectName = Dest.ProjectName" then its going into two Apply each conditions but still not creating items..

Answer 1

Nothing like searching for an answer to your specific problem and finding exactly one other person with the same issue, but no resolution.

Fortunately, I've managed to work out a decent solution for an SQL Upsert with an On-Premises SQL Connector in Power Automate.

Here's the general overview, I'll go through step-by-step after:

First step is to retrieve the single row by ID using Get row (V2).

The next step is to parse the JSON of the body of the previous call.

Here is the Schema that I used:

{
    "type": "object",
    "properties": {
        "status": {
            "type": "integer"
        },
        "message": {
            "type": "string"
        },
        "error": {
            "type": "object",
            "properties": {
                "message": {
                    "type": "string"
                }
            }
        },
        "source": {
            "type": "string"
        }
    }
}

Now the key bit, hit Configure Run After for the Parse JSON action and have it run on both Success and Failure of the previous action.

Then we add a conditional that checks the status code of the Get Row action (as output by the Parse JSON action). If it failed with a 404 Status, we do an Insert. Otherwise, do an Update.

Hopefully this helps anyone else trying to work around the limitations of the On-Premises connector.

Answer 2

Sharing a limitation that I ran into with both MySQL Get Row and Update Row because of my index column format -- my key is a triplet, like (9999,876,0).

Both Get Row and Update Row think that 3 keys are being passed because the contents have a comma in them.

MS support confirmed that this is a limitation, no special characters can be in the index contents, and no workaround.

I will have to figure out another tool set for my use case on this table...