I`m currently recieving data from sensor and storing it to Azure SQL database through Azure Stream Analytics job. My question is: how can I overwrite old data from sensor with new data? Sensor has its own ID, so is it possible to overwrite data by sensor ID.
You can use a MERGE statement to perform a conditional INSERT/UPDATE (sometimes called UPSERT) of data in your table.
MERGE INTO dbo.Output AS Target USING (SELECT DeviceId, ParkingStatus FROM IoInput) AS Source (Id, ParkingStatus) ON Target.Id = Source.Id WHEN MATCHED THEN UPDATE SET ParkingStatus = Source.ParkingStatus WHEN NOT MATCHED BY TARGET THEN INSERT (Id, ParkingStatus) VALUES (Id, ParkingStatus)
This statement will match records in IoInput with existing records in Output. Any matched records will update the ParkingStatus in Output with the value in IoInput. Any unmatched records in IoInput will be inserted into Output.