Azure SQL Database – Overwriting Old Data

azure-sql-database

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.

Best 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.

Related Question