October 28, 2020 at 4:45 pm
This is my 1st attempt at openjson & I'm trying to setup a stored proc that passes in multiple records and either inserts or updates the record in the table. I can setup the basic insert or update query using my openjson, however I don't know how to determine if the current record needs to be inserted or updated based on json values.
Here's a quick, basic example. I pass in json data with 2 orders that I want to insert/update in the dbo.Orders table. The 1st order I pass in has OrderId "123", so since I know the record already exists, I need to update it. The 2nd order however, has an OrderId = 0. So this one doesn't exist in the db & needs to be inserted. How would I do that?
DECLARE @json NVARCHAR(2048) = N'[
{
"Order": {
"OrderId":123,
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]'
SELECT * FROM OpenJson(@json);
--Here's where I need to do an insert/update. Not sure how, but here's the gist:
--If Json's Order.OrderId > 0
--BEGIN
-- UPDATE dbo.Orders WHERE OrderId = <Json's Order.OrderId>
--END
--ELSE
--BEGIN
-- INSERT INTO dbo.Orders (all values)
--END
October 28, 2020 at 5:28 pm
I suggest that you do it using this method (copied from https://sqlperformance.com/2020/09/locking/upsert-anti-pattern):
BEGIN TRANSACTION;
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE = @key;
IF @@ROWCOUNT = 0
BEGIN
INSERT dbo.t(, val) VALUES(@key, @val);
END
COMMIT TRANSACTION;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 28, 2020 at 7:39 pm
One issue you might run into when opening this JSON is some of the fields were given names which are SQL Server reserved words, for example 'Date' and 'Order'. There are 2 ways to deal with this when specifying the schema: 1) use brackets (i.e. [Date]...) (I detest brackets and always try to avoid them)) and 2) specify the path name. It's best practice imo to always rename the column(s) and assign them a non-reserved word label. Otherwise, if you don't want to use OPENJSON, you could pick out the individual fields by using JSON_VALUE and specifying the full path. You mentioned OPENJSON so that's what I used.
Since the JSON may contain both new and existing orders I think the code pattern should execute both the INSERT and UPDATE depending on WHERE and/or JOIN conditions.
Something like this
drop proc if exists test_json_upsert_multirow_proc;
go
create test_json_upsert_multirow_proc
@json nvarchar(max),
@test_bit bit output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
declare @o_count int;
/* insert new rows */
insert dbo.orders(AccountNumber, OrderNumber, OrderDate, Price, Quantity)
select oj.AccountNumber, ord_j.Number, ord_j.OrderDate, itm_j.Price, itm_j.Quantity
from openjson(@json) with (OrderJson nvarchar(max) N'$.Order' as json,
AccountNumber nvarchar(4000),
Item nvarchar(max) as json) oj
cross apply openjson(oj.OrderJson) with (OrderId int,
Number nvarchar(4000),
OrderDate datetime N'$.Date') ord_j
cross apply openjson(oj.Item) with (Price decimal(16, 4),
Quantity int) itm_j
where ord_j.OrderId is null;
select @o_count=@@rowcount;
/* update existing rows */
update o
set Price=itm_j.Price,
Quantity=itm_j.Quantity
from openjson(@json) with (OrderJson nvarchar(max) N'$.Order' as json,
AccountNumber nvarchar(4000),
Item nvarchar(max) as json) oj
cross apply openjson(oj.OrderJson) with (OrderId int,
Number nvarchar(4000),
OrderDate datetime N'$.Date') ord_j
cross apply openjson(oj.Item) with (Price decimal(16, 4),
Quantity int) itm_j
join dbo.orders o on itm_j.OrderId=o.OrderId;
select @o_count=@o_count+@@rowcount;
/* set the test bit */
if (@o_count>0)
select @test_bit=cast(1 as bit);
else
select @test_bit=cast(0 as bit);
commit transaction;
end try
begin catch
/* do something here. Rethrow, raiserror, log... */
select @test_bit=cast(0 as bit);
rollback transaction
end catch
go
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply