SQLTeam.com | Weblogs | Forums

SQL Server: Merge Operation with json source

tsql

#1

I actually have to save some data (json) from an API call in a SQL Server 2016 database. The system saves the json files in a directory, a JQ transformer changes the data structure and a "simple" merge command to update the table for two different scenarios:
the entry does not exist in the table --> insert the entry
the entry does exist in the table --> update price information
I build a SQL statement:

MERGE dbo.dreamlines_sails AS t
USING ((SELECT
nid, title, nights, zone, sails_nid, arrival, departure,
cabintype, catalogPrice, discountPrice, currency, discountPercentage
FROM
OPENROWSET (BULK 'C:\Powershell\test.json', SINGLE_CLOB) as j
CROSS APPLY
OPENJSON(BulkColumn)
WITH (nid int, title nvarchar(200), nights int, zone nvarchar(100), sails_nid int, arrival int, departure int, cabintype nvarchar(100), catalogPrice int, discountPrice int, currency nvarchar(100), discountPercentage float))) AS s ON t.sails_nid = s.sails_nid

WHEN MATCHED AND (t.nid = s.nid and t.title = s.title and t.cabintype = s.cabintype and t.catalogPrice = s.catalogPrice) 
   THEN 
       UPDATE SET t.discountPrice = s.discountPrice, 
                  t.catalogPrice = s.catalogPrice
WHEN NOT MATCHED 
   THEN
      INSERT (nid, nights,title, zone, sails_nid, arrival, departure, cabintype, catalogPrice, discountPrice, currency, discountPercentage)
      VALUES (s.nid, s.nights, s.title, s.zone, s.sails_nid, s.arrival, s.departure, s.cabintype, s.catalogPrice, s.discountPrice, s.currency, s.discountPercentage);

But I get the following error:

I thought I had done enough in/exclusions to verify an unique update per row (seems not). Any tips? I looked at that statement for a long time, and I still didn't find my mistake.
Regards Timo