Nano Hash - криптовалюты, майнинг, программирование

Хранимая процедура SQL Server с OPENJSON: как определить вставку или обновление?

Это моя первая попытка openjson, и я пытаюсь настроить хранимую процедуру, которая передает несколько записей и либо вставляет, либо обновляет запись в таблицу. Я могу настроить базовый запрос на вставку или обновление, используя мой openjson, однако моя проблема в том, что я не знаю, как определить, нужно ли вставлять или обновлять текущую запись на основе значений json.

Вот простой простой пример. Я передаю данные json с двумя заказами, которые я хочу вставить/обновить в таблицу dbo.Orders.

1-й заказ, который я передаю, имеет OrderId = 123, поэтому, поскольку я знаю, что запись уже существует, мне нужно ее обновить. Однако 2-й заказ имеет OrderId = 0. Так что этого нет в базе данных и его нужно вставить.

Как бы я это сделал?

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

Спасибо за любую помощь

sql-server sql-server-2016
28.10.2020

  • Какие значения вы конкретно ищете INSERT/UPDATE здесь? Вы видели MERGE? 28.10.2020

Ответы:


1

Как упомянул Ларну в своем комментарии, MERGE — это то, что вам нужно.

Вот пример, который вы можете запустить в SSMS:

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}}
]';

/* ORDERS TABLE MOCK-UP */
DECLARE @Orders table (
    OrderId int, OrderNumber varchar(10), OrderDate datetime, AccountNumber varchar(10), ItemPrice decimal(18,4), ItemQuantity int
);

/* INSERT A RECORD THAT WILL BE UPDATED BY THE MERGE */
INSERT INTO @Orders VALUES
    ( 123, 'SO43659', '2011-05-31 00:00:00.000', 'AW29825', 2024.9940, 5 ); -- Quantity will be updated to 1 via the MERGE.

/* SHOW ORDERS STARTING RESULTSET */
SELECT * FROM @Orders;

/* PERFORM ORDERS MERGE TO UPDATE/INSERT ROWS */
MERGE @Orders AS ord
USING (
    SELECT * FROM OPENJSON( @json ) WITH (
        OrderId int '$.Order.OrderId',
        OrderNumber varchar(10) '$.Order.Number',
        OrderDate datetime '$.Order.Date',
        AccountNumber varchar(10) '$.AccountNumber',
        ItemPrice decimal(18,4) '$.Item.Price',
        ItemQuantity int '$.Item.Quantity'
    )
) AS jsn
ON
    ord.OrderId = jsn.OrderId
WHEN MATCHED THEN
    UPDATE SET
        OrderNumber = jsn.OrderNumber,
        OrderDate = jsn.OrderDate,
        AccountNumber = jsn.AccountNumber,
        ItemPrice = jsn.ItemPrice,
        ItemQuantity = jsn.ItemQuantity
WHEN NOT MATCHED THEN
    INSERT ( OrderId, OrderNumber, OrderDate, AccountNumber, ItemPrice, ItemQuantity )
    VALUES ( jsn.OrderId, jsn.OrderNumber, jsn.OrderDate, jsn.AccountNumber, jsn.ItemPrice, jsn.ItemQuantity );

/* SHOW THE MERGED RESULTSET */
SELECT * FROM @Orders;

Начальный набор результатов @Orders:

+---------+-------------+-------------------------+---------------+-----------+--------------+
| OrderId | OrderNumber |        OrderDate        | AccountNumber | ItemPrice | ItemQuantity |
+---------+-------------+-------------------------+---------------+-----------+--------------+
|     123 | SO43659     | 2011-05-31 00:00:00.000 | AW29825       | 2024.9940 |            5 |
+---------+-------------+-------------------------+---------------+-----------+--------------+

После выполнения MERGE обновленный набор результатов @Orders выглядит следующим образом:

+---------+-------------+-------------------------+---------------+-----------+--------------+
| OrderId | OrderNumber |        OrderDate        | AccountNumber | ItemPrice | ItemQuantity |
+---------+-------------+-------------------------+---------------+-----------+--------------+
| 123     | SO43659     | 2011-05-31 00:00:00.000 | AW29825       | 2024.9940 |            1 |
| NULL    | SO43661     | 2011-06-01 00:00:00.000 | AW73565       | 2024.9940 |            3 |
+---------+-------------+-------------------------+---------------+-----------+--------------+

Вы можете видеть, что MERGE вставил новую запись для Number SO43661 и обновил OrderId 123 для . ItemQuantity от 5 (начальное значение) до 1.

29.10.2020
Новые материалы
Кластеризация: более глубокий взгляд
Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
Как написать эффективное резюме
Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
Частный метод Python: улучшение инкапсуляции и безопасности
Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
Как я автоматизирую тестирование с помощью Jest
Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..