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.– HandyDMay 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šisMay 13, 2019 at 20:02
1 Answer
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.
-
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? 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.– HandyDMay 15, 2019 at 0:28