0

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.

Improve this question
2
  • 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, 2019 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, 2019 at 20:02

1 Answer 1

Reset to default
0

You can use a MERGE statement to perform a conditional INSERT/UPDATE (sometimes called UPSERT) of data in your table.

Example:

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.

Improve this answer
2
  • 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, 2019 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, 2019 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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