# Insert array contents into MSSQL table

**URL:** <https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003>\
**Category:** General\
**Created:** [13 July 2020 14:14 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003 "2020-07-13T14:14:35Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 14:14 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/1 "2020-07-13T14:14:35Z")

</div>

Hello, may I please ask for some guidance... I have an array of data I want to insert into my MSSQL table called 'MyTable', here is the statement I have created:

```auto
INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) VALUES ('{{{payload.object.id}}}', '{{{payload.object.deviceId}}}', '{{{payload.object.label}}}', '{{{payload.object.message}}}', '{{{payload.object.serverity}}}', '{{{payload.object.activatedTime}}}', '{{{payload.object.clearedTime}}}')

```

Here is a screen shot of my Flow:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/2/227f8dd6b6c80810d15ca154f4204951469c505d.png)

I think the following is going into my SQL Node:

```auto
13/07/2020, 14:12:52node: bb71a7c8.4d6348
msg.payload : array[2]
array[2]
0: object
id: "49cd2d85-0e12-4147-8bfa-444eb43d7a4e"
deviceId: "a03394b0-9a49-45c7-afb4-07541a8aba47"
label: "Handle Alarmed"
message: "Rack Door Handle alarmed for sensor Back Door at NBRK0750 with state "
severity: "CRITICAL"
activatedTime: "2020-06-19T10:38:24.199Z"
1: object
id: "9dadad16-38fe-4ee8-9f65-ada755a97e10"
deviceId: "acb8c96e-db30-4b72-bd06-ab8706b43efd"
label: "Local Authorization Alarm"
message: "Local Authorization alarm for Back Door at NBRK0750"
severity: "INFO"
activatedTime: "2020-04-29T14:28:57.594Z"

```

Thank you!

---

<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:** [13 July 2020 14:41 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/2 "2020-07-13T14:41:09Z")

</div>

can you attach a debug node (set to show complete message) to the output of the MSSQL node

I need to see the all the msg values (in particular the SQL generated by the mustache)

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 14:45 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/3 "2020-07-13T14:45:26Z")

</div>

Hello again Steve-Mcl 🙂 Here is the result of the debug node:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/6/760de82e6df0f3135f1870e72ed9e19f95b58a74.png)

Here is my SQL table when it fails:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/e/3ed28aa9c94eeb59f83dbf64c458b92eac9823bf.png)

Thank you.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [13 July 2020 14:58 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/4 "2020-07-13T14:58:39Z")

</div>

I wonder what that error means. Have you tried googling for it?

---

<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:** [13 July 2020 15:01 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/5 "2020-07-13T15:01:47Z")

</div>

I guess you are not using [node-red-contrib-mssql-plus](https://flows.nodered.org/node/node-red-contrib-mssql-plus)

when you use that node (apart from the many bug fixes & additional features) you get the compiled SQL statement in the debug output to help you see what is going wrong.

If you wish to upgrade...

- stop node-red
- open a command line prompt & CD to your node-red folder
- `npm uninstall node-red-contrib-mssql` (or which ever derivative you are using)
- `npm install node-red-contrib-mssql-plus`
- start node-red

---

<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:** [13 July 2020 15:04 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/6 "2020-07-13T15:04:18Z")

</div>

Colin, i suspect the values are not being rendered into his SQL Statement (I suspect the duplicate violation is a red herring - and why I am asking him to install mssql-plus as it gives you the rendered SQL statement in the output msg object to see what is _actually_ being executed against the SQL Server)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [13 July 2020 15:05 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/7 "2020-07-13T15:05:22Z")

</div>

That makes sense.

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:06 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/8 "2020-07-13T15:06:07Z")

</div>

Hi Steve-Mcl, I am actually using the 'node-red-contrib-mssql-plus' node:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/c/bcfcc29b80ddf8deb597907e6fe42da500e32b73.png)

---

<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:** [13 July 2020 15:06 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/9 "2020-07-13T15:06:59Z")

</div>

where did you attach the debug?

can you share your flow

`````  
`like this`  
`````

and a screen shot

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:08 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/10 "2020-07-13T15:08:50Z")

</div>

Here is the Flow:

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:09 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/11 "2020-07-13T15:09:35Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/7/a7b866dfb0cefb75cdb31472accee25d946a5c70.png)

---

<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:** [13 July 2020 15:10 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/12 "2020-07-13T15:10:36Z")

</div>

Yeah - you need to show complete message option set on the debug node

> [@Steve-Mcl](#):
>
> can you attach a debug node (set to show complete message)

Try again please.

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:15 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/13 "2020-07-13T15:15:04Z")

</div>

Here is the debug output now:

```auto
13/07/2020, 16:13:17node: 734d2b68.1ad644
msg : Object
object
topic: ""
payload: object
headers: object
statusCode: 200
responseUrl: "https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/alarms"
redirectList: array[0]
parts: object
_msgid: "6a91ebff.7d9cb4"
query: "INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) VALUES ('', '', '', '', '', '', '')↵"

```

and a new screen shot:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/c/dc95f78264484e344e0abd1af3742513e751c1b5.png)

---

<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:** [13 July 2020 15:20 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/14 "2020-07-13T15:20:13Z")

</div>

> [@jomacdon](#):
>
> `query: "INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) VALUES ('', '', '', '', '', '', '')"`

So as you can see - all of your values are empty

so the duplicate violation is occurring because you are always putting empty strings in the database.

Since your SQL is...

```auto

INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) 
VALUES ('{{{payload.object.id}}}', '{{{payload.object.deviceId}}}', '{{{payload.object.label}}}', '{{{payload.object.message}}}', '{{{payload.object.serverity}}}', '{{{payload.object.activatedTime}}}', '{{{payload.object.clearedTime}}}')

```

... check the output of the template node **Place each into X** - does it have properties in `payload.object.deviceId` and `payload.object.id` etc?

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:40 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/15 "2020-07-13T15:40:22Z")

</div>

Hi, thanks for your patience 🙂  
Here is another screen shot showing the original output and then after the Split & Template:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/c/2c9e4cd79cdc7fd997fffc85f42f71fe3a537bf9.png)  
I tested the output from the debug node looking for 'payload.object.id' as you suggested and I get nothing:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/9/6925bbad66ee1d21b9d3f4bbd65a6d5c40ef3a86.png)

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:45 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/16 "2020-07-13T15:45:59Z")

</div>

Inside Split No.1 I have two Objects in the Array, each has a key identifier with a corresponding value, it's these values I would like to Inject into my SQL table.

---

<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:** [13 July 2020 15:46 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/17 "2020-07-13T15:46:10Z")

</div>

your split isnt working.

Can you paste a copy (using the debug copy button) of the data from the API node

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/e/5e9198bd7626407180ed54eeab1ebf1e3df81e10.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/8/18750c2354576dfcdeb10f3b94e519151ec76b8a.png)

`````  
`paste copy from debug like this`  
`````

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:51 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/18 "2020-07-13T15:51:07Z")

</div>

Hi Steve-Mcl, thanks! Here is the debug output from the API call:

```auto
13/07/2020, 16:49:24node: acce12ef.05cb8
msg.payload : Object
object
alarms: array[297]
[0 … 9]
[10 … 19]
[20 … 29]
[30 … 39]
[40 … 49]
[50 … 59]
[60 … 69]
[70 … 79]
[80 … 89]
[90 … 99]
[100 … 109]
[110 … 119]
[120 … 129]
[130 … 139]
[140 … 149]
[150 … 159]
[160 … 169]
[170 … 179]
[180 … 189]
[190 … 199]
[200 … 209]
[210 … 219]
[220 … 229]
[230 … 239]
[240 … 249]
[250 … 259]
[260 … 269]
[270 … 279]
[280 … 289]
[290 … 296]
offset: "14588213"

```

---

<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:** [13 July 2020 15:54 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/19 "2020-07-13T15:54:42Z")

</div>

I cant do anything with that data.

Can you do exactly what I asked & I will see if I can make this work for you by "faking the API call" (since i dont have access to the API, I need ACTUAL data - not a bunch of strings saying `[0 … 9]`)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/8/18750c2354576dfcdeb10f3b94e519151ec76b8a.png)  
^ PRESS THE COPY BUTTON - PASTE INTO REPLY 🙂

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [13 July 2020 15:59 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/20 "2020-07-13T15:59:46Z")

</div>

Sorry - still getting to grips with this...

I got an error with the size of the reply (Body is limited to 32000 characters; you entered 94735.)

Here is the output after the filter node instead:

```auto
{"alarms":[{"id":"49cd2d85-0e12-4147-8bfa-444eb43d7a4e","deviceId":"a03394b0-9a49-45c7-afb4-07541a8aba47","label":"Handle Alarmed","message":"Rack Door Handle alarmed for sensor Back Door at NBRK0750 with state ","severity":"CRITICAL","activatedTime":"2020-06-19T10:38:24.199Z"},{"id":"9dadad16-38fe-4ee8-9f65-ada755a97e10","deviceId":"acb8c96e-db30-4b72-bd06-ab8706b43efd","label":"Local Authorization Alarm","message":"Local Authorization alarm for Back Door at NBRK0750","severity":"INFO","activatedTime":"2020-04-29T14:28:57.594Z"}],"offset":"14588213"}

```

[Next page](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003.md?page=2)