Forum Discussion
Azure ADX - UpdatePolicy fails to insert data
Hi Everyone,
I believe everyone is doing good and safe.
I am facing challenge with ADX. Please find the problem details below.
Problem statement: We are unable to insert a result data into a target table from source table using an UpdatePolicy.
Description: We have written an UpdatePolicy on a table. This UpdatePolicy will accept query parameters as an ADX function. This function returns output result in the form of table. Further, This table output result received should be inserted into target table.
Additional Details:
- UpdatePolicy helps to update the data into a target table from source table dynamically/automatically.
- UpdatePolicy is almost equivalent to Triggers in SQL Server to do dynamic insert into a target table.
- Syntax of UpdatePolicy
.alter table TargetTable policy update
```
[
{
"IsEnabled": true,
"Source": "SourceTable",
"Query": "SourceTable
| extend Result = G3MS_ClearAlarm(Id, CountryCode, OccuredTime)
| project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, ClearTime = Result.ClearTime",
"IsTransactional": true,
"PropagateIngestionProperties": false
}
]
```
Error Received when executed
Error during execution of a policy operation: Request is invalid and cannot be processed: Semantic error: SEM0085: Tabular expression is not expected in the current context.
If anyone has any suggestions/thoughts on this will be very beneficial to complete the requirement.
1 Reply
Isn't this happing because you're trying to assign a full table (returned by G3MS_ClearAlarm(...)) to a scalar column (Result)?
Assuming G3MS_ClearAlarm(...) returns a table with Id, and SourceTable has a corresponding Id key column, can you try this?
.alter table TargetTable policy update
[
{
"IsEnabled": true,
"Source": "SourceTable",
"Query": "SourceTable
| join kind=inner (
G3MS_ClearAlarm()
) on Id
| project AlarmId, ClearAlarmId, ClearTime",
"IsTransactional": true,
"PropagateIngestionProperties": false
}
]
