Stored Proc with OPENJSON: How to determine insert or update?

  • 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

    • This topic was modified 1 month ago by  Goalie35.
  • 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;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

     

    • This reply was modified 1 month ago by  Steve Collins. Reason: Brackets also work to open JSON fields labeled with SQL Server reserve words

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 3 posts - 1 through 3 (of 3 total)

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