Use output of let statements to append it in a table in ADX

Question

I have written few let statements and wanted to add the output of those statements to a table , i tried using .append command but it throws error and i followed the Microsoft documents but it doesn't work for me , how can i achieve this?

sample query -

.append TestTable<| 

let equipment=<query>
|join kind=inner
(Table2)
on $left.LookupId==$right.LookupId
|extend  NameEq=strcat(PreFix,".",Value,".",Id)
|project NameEq
;

let inCycle=
Table1
| where Name in (equipment) and Value in ('True','False') and TimeStamp between (datetime(2023-05-24 00:00:00) .. datetime(2023-05-24 23:59:59))
| order by TimeStamp asc
|project Name,TimeStamp,Value,ConnectionDeviceId
|extend  KPIValue=iff(Value =='False' and Name ==prev(Name),TimeStamp- prev(TimeStamp),totimespan(0))
|project   KPIValue=KPIValue/1s,ConnectionDeviceId,TimeStamp
;

let result=
inCycle
|project Id=new_guid(),ConnectionDeviceId='Device1',KpiName='Uptime',KPIValue,Name='Test',StartDateTime=now(),EndDateTime=now();

result

other sample query -

let equipment=<query>
|join kind=inner
(Table2)
on $left.LookupId==$right.LookupId
|extend  NameEq=strcat(PreFix,".",Value,".",Id)
|project NameEq
;

let inCycle=
Table1
| where Name in (equipment) and Value in ('True','False') and TimeStamp between (datetime(2023-05-24 00:00:00) .. datetime(2023-05-24 23:59:59))
| order by TimeStamp asc
|project Name,TimeStamp,Value,ConnectionDeviceId
|extend  KPIValue=iff(Value =='False' and Name ==prev(Name),TimeStamp- prev(TimeStamp),totimespan(0))
|project   KPIValue=KPIValue/1s,ConnectionDeviceId,TimeStamp
;

let result=
inCycle
|project Id=new_guid(),ConnectionDeviceId='Device1',KpiName='Uptime',KPIValue,Name='Test',StartDateTime=now(),EndDateTime=now();


.append TestTable <|
result

Both of the above queries are giving error
Note - query output and table output are same

Answer 1

@siddharth bansal Thanks for using Microsoft Q&A forum and posting your query.

Looks like the append syntax is incorrect and resulting in an error. Could you please try below and see if that helps.

let equipment=<query>
|join kind=inner
(Table2)
on $left.LookupId==$right.LookupId
|extend  NameEq=strcat(PreFix,".",Value,".",Id)
|project NameEq
;

let inCycle=
Table1
| where Name in (equipment) and Value in ('True','False') and TimeStamp between (datetime(2023-05-24 00:00:00) .. datetime(2023-05-24 23:59:59))
| order by TimeStamp asc
|project Name,TimeStamp,Value,ConnectionDeviceId
|extend  KPIValue=iff(Value =='False' and Name ==prev(Name),TimeStamp- prev(TimeStamp),totimespan(0))
|project   KPIValue=KPIValue/1s,ConnectionDeviceId,TimeStamp
;

let result=
inCycle
|project Id=new_guid(),ConnectionDeviceId='Device1',KpiName='Uptime',KPIValue,Name='Test',StartDateTime=now(),EndDateTime=now();

TestTable
| set-or-append result

OR

.set-or-replace TestTable <| 
let equipment=<query>
|join kind=inner
(Table2)
on $left.LookupId==$right.LookupId
|extend  NameEq=strcat(PreFix,".",Value,".",Id)
|project NameEq
;

let inCycle=
Table1
| where Name in (equipment) and Value in ('True','False') and TimeStamp between (datetime(2023-05-24 00:00:00) .. datetime(2023-05-24 23:59:59))
| order by TimeStamp asc
|project Name,TimeStamp,Value,ConnectionDeviceId
|extend  KPIValue=iff(Value =='False' and Name ==prev(Name),TimeStamp- prev(TimeStamp),totimespan(0))
|project   KPIValue=KPIValue/1s,ConnectionDeviceId,TimeStamp
;

let result=
inCycle
|project Id=new_guid(),ConnectionDeviceId='Device1',KpiName='Uptime',KPIValue,Name='Test',StartDateTime=now(),EndDateTime=now();

TestTable
| union result

If you use .append command, then the query will fail if the table doesn't exist. doesn't exist. Whereas if you use .set-or-replace or .set-or-append table will be created if it doesn't exist.

For more info please refer to this document: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/data-ingestion/ingest-from-query

Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

I want to add some info related to the append

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/data-ingestion/ingest-from-query

• The .append command in Kusto is used to add data to an existing table. The command fails if the table does not exist​
• When you use the .append command, you need to specify the name of the table you want to append data to, and the query or command whose results you want to ingest into the table​
• The .append command, as well as .set-or-append and .set-or-replace commands, preserve the schema of the table by default, unless the extend_schema ingestion property is set to true​
• The results of your query must match the schema of the target table, based on the column types. There's no matching of column names, so the query result schema columns should be in the same order as the table, or else data will be ingested into the wrong columns​
• If the query generates an entity name with the $ character, the command will fail. The entity names must comply with the naming rules, so the $ character must be removed for the ingest command to succeed​
• Data ingestion is a resource-intensive operation that might affect concurrent activities on the cluster, including running queries. Therefore, avoid running too many ingestion commands at the same time. Limit the data for ingestion to less than 1 GB per ingestion operation. If necessary, use multiple ingestion commands​

and a simple example

.append OldExtents with(tags='["TagA","TagB"]') <| 
   MyExtents 
   | where CreatedOn < now() - time(30d) 
   | project ExtentId

In your case, you should be able to use a similar approach to append the result of your let statements to a table, making sure that the result of your let statements match the schema of the target table.