How to insert a row into SQL Server from Power Automate with dynamic table name?

I have created a small test Power Flow that's manually triggered, accepts one input and inserts that into a SQL Server table using the SQL Server Connector Insert row (V2) and all is happy.

However, I need to be able to dynamically change the table name, because our corporate environment has separate Dev, QA, and Prod environments, and I need the same flow to work in any of the three environments by simply changing a PA Environment Variable.

When I modify the flow to use either a predeclared Variable (through Dynamic Content) or an Expression, the Insert Row (V2) suddenly demands a Row ID and also tells me that my data item is no longer available.

Here is the flow with a statically selected table that works just fine:

Here's what happens when I use a variable for the table name:

You can see that it now wants a row ID, but there isn't a row ID for a row I'm about to insert. I entered 1 for the RowID, but that'll only work on the very first insert (if it were to work at all). It also doesn't ask for data, so when I attempt a test run, it fails for lack of data.

Here's the actual error message that's returned:

A value must be provided for item.
clientRequestId: 7398bd76-75f7-41b1-b23d-8b9279737e4b

Is it possible to use a dynamic table name and if so, how do I go about doing so?

Taking Skin's advice, I added a Get Row V2 to my flow to get the JSON for my table:

I copied the JSON provided in the OUTPUTS section:

And pasted that into the Row parameter for the Insert Row (V2) step:

When I try to save the flow it tells me:

Fix invalid expression(s) for the input
parameter(s) of operation 'Insert_row_(V2)'.

It seems quite obvious to me that hard coding a value of 1 for the "ID" field is completely invalid since it's an IDENTITY column.

Do I need to drop the "@odata.context" and/or "@odata.etag" elements from the JSON? If so, that leaves me with just the column definitions (which I'd tried entering by hand) and it didn't like that, either.

Another attempt, with just columns defined in the Row field, leaving out the IDENTITY column:

Still gives me an error:

The key didn't match any rows in the table. inner exception: The key didn't match any rows in the table.
clientRequestId: 0064a03c-81c5-4928-b7fb-bf0f8cdc609a

Also fails if I hard-code a value into the Rows field instead of using the variable.

After finally groking the table naming issue and getting that sorted:

The run is still failing with exactly the same error:

The table definition:

CREATE TABLE testSchema1.Test1_DEV (
    ID INT PRIMARY KEY IDENTITY,
    DataField nvarchar(255)
    )
go

Yes, I'm 100% certain that the obfuscated server & database names are correct. I've confirmed them and I'm using the same connection that's working in another flow that's calling a stored proc (and is not using any dynamic table naming). What other obvious thing(s) am I missing?

I've attempted to simplify further. I removed the dynamic table name from the flow, and I recreated the table in the [dbo] schema to ensure that there weren't any rights/access issues.

Table definition:

CREATE TABLE Test1_DEV (
    ID INT PRIMARY KEY IDENTITY,
    DataField nvarchar(255)
    )
go

Then I modified the Insert Row step to explicitly select the table by name, instead of using any Compose, Variable or other possibly confounding factors, but I get an error upon selecting the table name:

I then deleted the Insert Row step from the flow and added a new, fresh one in. Now, it cannot find the table at all:

Even though the table is quite visible in SSMS:

Solution

It definitely works, you're misreading the purpose of the Row parameter.

It accepts a JSON object that represents your table structure with values.