Upsert JSON documents in SQL Server 2016

In Sql Server 2016 you can easily insert or update JSON documents by combining OPENJSON and MERGE statement. In this example I will create one procedure that imports events formatted as JSON in Events table.

Example code is shown in the following listing. I’m creating one table that will contain events and procedure that will de-serialize events formatted as JSON and upsert them into destination table.

-- Drop sp_ImportEvents stored procedure 
DROP PROCEDURE IF EXISTS dbo.sp_ImportEvents 
GO 
 
-- Drop Events table 
DROP TABLE IF EXISTS dbo.Events 
GO 
 
-- Create Events table 
CREATE TABLE dbo.Events( 
    EventId int PRIMARY KEY, 
    DeviceId int NOT NULL, 
    Value int NOT NULL, 
    Timestamp datetime2(7) NULL
)
GO
-- Create sp_InsertEvents stored procedure 
CREATE PROCEDURE dbo.sp_ImportEvents  
    @Events NVARCHAR(MAX)
AS  
BEGIN 
    MERGE INTO dbo.Events AS A 
    USING ( 
        SELECT *
        FROM OPENJSON(@Events) WITH (EventId int, DeviceId int, Value int, Timestamp datetime2(7))) B
       ON (A.EventId = B.EventId) 
    WHEN MATCHED THEN 
        UPDATE SET A.DeviceId = B.DeviceId, 
                   A.Value = B.Value, 
                   A.Timestamp = B.Timestamp 
    WHEN NOT MATCHED THEN 
        INSERT (EventId, DeviceId, Value, Timestamp)  
        VALUES(B.EventId, B.DeviceId, B.Value, B.Timestamp); 
END 
GO

 

Events are provided as JSON array (or single JSON object) that has EventId, DeviceId, Value, and Timestamp properties. OPENJSON will open this document and transform it to table results. Then you can provide this resultset to MERGE statement where you can insert new row or update existing based on the Id column.

Now you can easily import one or many JSON documents into Event table:

EXEC sp_ImportEvents N'{"EventId":1,"DeviceId":17,"Value":35,"Timestamp":"2015-10-23 12:45:37.1237"}'
SELECT * FROM Events

GO

EXEC sp_ImportEvents 
N'[{"EventId":1,"DeviceId":17,"Value":37,"Timestamp":"2015-10-23 12:45:37.1237"},
{"EventId":2,"DeviceId":17,"Value":35,"Timestamp":"2015-10-23 12:45:37.1237"}]'
SELECT * FROM Events