SQL Server2016: Merge Operation with json source

Question

SQL Server2016: Merge Operation with json source

SSC Veteran

Points: 269

June 9, 2017 at 8:25 am

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 tUSING ((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_nidWHEN 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.catalogPriceWHEN 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:

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

First of all, here's that query again, for those of us with screens less than 3m wide 🙂
MERGE dbo.dreamlines_sails 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)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 )) ) 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 );

Is sails_nid a unique column in both target and source?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

timocordes 14433 SSC Veteran Points: 269 More actions · Answer 2

Hi Phill,

thanks, sorry for the Format.
The sails nid is not unique (it is like a product id). The combination of sails_nid and catalogprice is unique.

Regards
Timo

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

If that's the best identifier you have, then I would expect your query to look more like this
MERGE dbo.dreamlines_sails 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)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 )) ) s ON t.sails_nid = s.sails_nid AND t.catalogPrice = s.catalogPrice WHEN MATCHED THEN UPDATE SET t.nid = s.nid , t.title = s.title , t.nights = s.nights , t.zone = s.zone , t.arrival = s.arrival , t.departure = s.departure , t.cabintype = s.cabintype , t.discountPrice = s.discountPrice , t.currency = s.currency , t.discountPercentage = s.discountPercentage 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 );

What is the primary key of the target table ... is it really (sails_nid, catalog_price)? If so, what is the function of nid? Can we use that instead?
You should note that if you are matching on CatalogPrice, updates to CatalogPrice in the source will always result in a new row being inserted to the target table. Is that what you want?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

timocordes 14433 SSC Veteran Points: 269 More actions · Answer 4

Hi Phil.

Thanks for your Input. I will try that Statement tomorrow if i am back on my development enviroment.
Yes both (sails_nid and catalogprice) are my identifier. The catalogprice never Change for a sails_nid so that didn't exists.
I have also the possibility to take the cabin_name as (additional) identifier. The combination of sails_nid, cabin_name and catalogPrice is also unique.
The nid is an identifier of a Group of sails_nid. So it is also not unique.

regards
Timo

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

timocordes 14433 - Friday, June 9, 2017 10:04 AM
Hi Phil.
Thanks for your Input. I will try that Statement tomorrow if i am back on my development enviroment.
Yes both (sails_nid and catalogprice) are my identifier. The catalogprice never Change for a sails_nid so that didn't exists.
I have also the possibility to take the cabin_name as (additional) identifier. The combination of sails_nid, cabin_name and catalogPrice is also unique.
The nid is an identifier of a Group of sails_nid. So it is also not unique.
regards
Timo

OK, hope it helps.
Note that if (sails_nid, catalog_price) is already enough for uniqueness, it is unnecessary to add any more columns and I would recommend against doing so.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

timocordes 14433 SSC Veteran Points: 269 More actions · Answer 6

Hi Phil,
Works like a charm! You made day. Thanks a lot!

regards timo

Sergiy SSC Guru Points: 110208 More actions · Answer 7

timocordes 14433 - Friday, June 9, 2017 10:04 AM
The catalogprice never Change for a sails_nid so that didn't exists.

Are you sure?
From your query:
UPDATE SET t.discountPrice = s.discountPrice , t.catalogPrice = s.catalogPrice
Looks very much like being changed.

_____________
Code for TallyGenerator

timocordes 14433 SSC Veteran Points: 269 More actions · Answer 8

Hi Guys,

I was to fast with the accomplish Report.
With the bulk Import i got two Errors with that Statement:

1. UTF CODEPAGE Errors on the title, Zone (both NVARCHAR)
The source data are in UTF8 Format saved and includes several german mutations (ä,ö...) After Import with the query i got several Codepage Errors on that mutations. I sav

2. the Zone is a list of entries (json Array) but the field is empty after Import (NULL). On my Experiments with SSIS the Import of that was that list still a string (that is the expected result). Any tips?

@sergiy
Small mistake in my query. Only the discountprice can change! Thanks for your Input!

Regards
Timo