# Payload to SQL query

**URL:** <https://discourse.nodered.org/t/payload-to-sql-query/51959>\
**Category:** General\
**Tags:** database\
**Created:** [7 October 2021 09:07 UTC](https://discourse.nodered.org/t/payload-to-sql-query/51959 "2021-10-07T09:07:47Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tombost](https://avatars.discourse-cdn.com/v4/letter/t/a587f6/32.png) [@tombost](https://discourse.nodered.org/u/tombost)\
**Post date:** [7 October 2021 09:07 UTC](https://discourse.nodered.org/t/payload-to-sql-query/51959/1 "2021-10-07T09:07:47Z")

</div>

Hello,

I have a big payload with 400 data like this :  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/a/ca91e77ce50b78030df162c370730c3c24c92f6b.png)

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 October 2021 09:24 UTC](https://discourse.nodered.org/t/payload-to-sql-query/51959/2 "2021-10-07T09:24:17Z")

</div>

> [@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)

---

<div class="post-metadata">

**Author:** ![tombost](https://avatars.discourse-cdn.com/v4/letter/t/a587f6/32.png) [@tombost](https://discourse.nodered.org/u/tombost)\
**Post date:** [7 October 2021 09:40 UTC](https://discourse.nodered.org/t/payload-to-sql-query/51959/3 "2021-10-07T09:40:36Z")

</div>

Ok I will see

But I have found a solution:

```auto
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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 December 2021 09:41 UTC](https://discourse.nodered.org/t/payload-to-sql-query/51959/4 "2021-12-06T09:41:35Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.