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.

  • Can you provide more specifics? The table schema including keys and constraints, sample incoming data and what columns you want to overwrite would be helpful. In most cases, a simple UPDATE statement should suffice, but without knowing anything about the tables this question is difficult to answer. – HandyD May 12 '19 at 21:34
  • SELECT DeviceId as id, parkingStatus INTO [Output] FROM [iotInput] Right now I insert data to database in this format. I want to overwrite variable "parkingStatus" if I get new data from sensor with same "id" – Mantas Juodviršis May 13 '19 at 20:02

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  
    UPDATE SET ParkingStatus = Source.ParkingStatus  
    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.

  • Thank you very much. One more question, can I insert this at "Steam Analytics" -> "Query"? And If I want to insert "parkingStatus" record to another table at the same time, I should perform normal "SELECT" as I have mentioned in my previous comment? – Mantas Juodviršis May 14 '19 at 16:46
  • I'm not sure what you mean by "Steam Analytics" -> "Query" sorry. Regarding the insert into another table, you could simply run another insert in the same batch. Depending on your requirements, use the transaction isolation level and explicit transactions to ensure a consistent view of data when merging and inserting your data. – HandyD May 15 '19 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.