Payload to SQL query

[tombost]

Hello,

I have a big payload with 400 data like this :

How can I convert this payload to have a query like this:

insert into data1 values ('DB1.DI0','1','ok'), ('DB1.DI4','2','ok'), ('DB1.DI8','3','ok'), ('DB1.DI12','4','ok'), ... and so on.

I have try to split my payload into multiple message and create a query for each message, this is working but I have too much data and some data are missing.
SO that's why I want to send only one big query to my MSSQL Server.

Thanks for help

[Steve-Mcl]

@tombost

SO that's why I want to send only one big query to my MSSQL Server.

Node-red-contrib-mssql-plus supports bulk mode and TVPs

There is a demo in the built in examples (CTRL+I)

[tombost]

Ok I will see

But I have found a solution:

msg.query = "insert into data1 values "

var suite = "";

for (var key in msg.payload) {
    
  if (msg.payload.hasOwnProperty(key)) {
    var val = msg.payload[key];
    suite += "('" + key + "','" + val + "','" + Time + "'),"
    
  }
}
msg.suite = suite
msg.payload = msg.query + msg.suite.slice(0, -1)

And now I can store 400 values every 800ms to the SQL database