CODEWITHSUNDEEP
javascriptsqlsql-servernode.jssequelize.js
tacotuesday
tacotuesday

Reputation: 135

Sequelize: Bulk update or insert into MSSQL DB

I have spent the last 24 hours combing the internet for a solution and have yet to find one. I am trying to do a bulk upsert (update or insert) of a single model into a mssql db. bulkCreate with updateOnDuplicate:true does not work with mssql. Is there any other solution? This is dataset is anywhere between 1-50k rows. Any help would be greatly appreciated!

Upvotes: 0

Views: 2973

Answers (2)

Krishna Dixit
Krishna Dixit

Reputation: 31

Convert your data into a table using OpenJson and merge with the source table as done below:

const upsertedData = [
  {
    id: "123",
    value: "Value 2"
  },
  {
    id: "124",
    value: "Value 1"
  }
]

const upsert = async (data) => {
  await sequelize.query(
    `BEGIN
      DECLARE @json NVARCHAR(MAX);
      SET @json = :dataToBeUpserted
      MERGE INTO dbo.tableToBeUpserted AS Target
      USING (SELECT * from OpenJson(@json) WITH (
          id nvarchar(32),
          value nvarchar(32),
      )) AS Source
      ON (Target.id = Source.id)
      WHEN MATCHED THEN
      UPDATE SET 
          Target.value = Source.value
      WHEN NOT MATCHED THEN           
          INSERT (id, value)
          VALUES (Source.id, Source.value);
    END`,
    {
      replacements: {
        dataToBeUpserted: JSON.stringify(data)
      },
    }
  )
}

await upsert(upsertedData)

Upvotes: 3

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89090

For SQL 2016+ you can simply pass your bulk data as JSON using a NVarchar(max) parameter, and parse and load it on the server. Your query would look something like this:

Insert into TargetTable(Number,Date,Customer,Quantity,[Order])
SELECT Number,Date,Customer,Quantity,[Order]
FROM OPENJSON ( @json )  
WITH (   
              Number   varchar(200)   '$.Order.Number',  
              Date     datetime       '$.Order.Date',  
              Customer varchar(200)   '$.AccountNumber',  
              Quantity int            '$.Item.Quantity',  
              [Order]  nvarchar(MAX)  AS JSON  
 );

And you would bind the @json parameter with a NVarchar(max) (string) value like:

[  
  {  
    "Order": {  
      "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  
    }  
  }
]

An update would look something like:

 with q as
 (
    select t.Number, t.Quantity oldQuantity, j.Quantity newQuantity 
    from OPENJSON ( @json )  
    WITH (   
                  Number   varchar(200)   '$.Order.Number',  
                  Date     datetime       '$.Order.Date',  
                  Customer varchar(200)   '$.AccountNumber',  
                  Quantity int            '$.Item.Quantity',  
                  [Order]  nvarchar(MAX)  AS JSON  
          ) j
    join TargetTable t
      on j.Number = t.Number
 )
 update q set oldQuantity = newQuantity

See: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

If you can't bind a parameter, then in a pinch you an include the JSON document as a literal string in your TSQL batch.

Upvotes: 0

Related Questions