# 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:** 5\
**Page:** 2

<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 16:04 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/21 "2020-07-13T16:04:12Z")

</div>

Is this OK for 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 16:13 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/22 "2020-07-13T16:13:26Z")

</div>

Yes.

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

Try this...

```auto
[{"id":"f95b91f8.8471e","type":"inject","z":"a07b3581.696e08","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":540,"y":1400,"wires":[["c1f4ea1.a942e18"]]},{"id":"c1f4ea1.a942e18","type":"http request","z":"a07b3581.696e08","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://172.17.0.115/api/current/kpis/latest?asset-id=6fa8b258-0713-4fdb-9965-f4beec7b56e4&type=POWER_CAPACITY","tls":"","persist":false,"proxy":"","authType":"basic","x":710,"y":1400,"wires":[["345e5ec.ff3a3a2"]]},{"id":"345e5ec.ff3a3a2","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":890,"y":1400,"wires":[]},{"id":"aecdaa58.9027f8","type":"inject","z":"a07b3581.696e08","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":200,"y":100,"wires":[["1e22e438.94c14c"]]},{"id":"ab363ea7.17d48","type":"http request","z":"a07b3581.696e08","name":"RESTful API Get Alarms","method":"GET","ret":"obj","paytoqs":false,"url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/alarms","tls":"","persist":false,"proxy":"","authType":"bearer","x":570,"y":100,"wires":[["7726a377.1f746c"]]},{"id":"1e22e438.94c14c","type":"function","z":"a07b3581.696e08","name":"Auth Key","func":"msg.headers = { \n \"Content-Type\": \"application/json\"\n };\n\n\nmsg.payload = {auth_key:\"069e2b9c-682d-4780-83c2-d87fe07e87dc\"};\nreturn msg;","outputs":1,"noerr":0,"x":360,"y":100,"wires":[["ab363ea7.17d48"]]},{"id":"d24f6cce.e58a3","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":990,"y":180,"wires":[]},{"id":"7726a377.1f746c","type":"function","z":"a07b3581.696e08","name":"Filter","func":"\n\nmsg.payload = msg.payload.alarms.filter(function( obj ) {\n return !obj.clearedTime;\n});\nreturn msg;","outputs":1,"noerr":0,"x":770,"y":100,"wires":[["91a36c6a.043f8","d24f6cce.e58a3","9e54d0d8.2acf9"]]},{"id":"91a36c6a.043f8","type":"function","z":"a07b3581.696e08","name":"Count Active Alarms","func":"var newMsg = { payload: msg.payload.alarms.length };\nreturn [newMsg, msg];","outputs":1,"noerr":0,"x":1020,"y":100,"wires":[[]]},{"id":"50d7b36d.53466c","type":"http request","z":"a07b3581.696e08","name":"RESTful API Get Inventory","method":"GET","ret":"obj","paytoqs":false,"url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/inventory","tls":"","persist":false,"proxy":"","authType":"bearer","x":560,"y":140,"wires":[["d24f6cce.e58a3"]]},{"id":"7fba9d7e.1c8b04","type":"http request","z":"a07b3581.696e08","name":"RESTful API Get Sensors","method":"GET","ret":"obj","paytoqs":false,"url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/sensors","tls":"","persist":false,"proxy":"","authType":"bearer","x":570,"y":180,"wires":[["d24f6cce.e58a3"]]},{"id":"cc3ee9c6.db6208","type":"MSSQL","z":"a07b3581.696e08","mssqlCN":"a51e405c.10f64","name":"SQL Server","query":"INSERT INTO [MyTable] (id, name, quantity) VALUES ({{{payload.id}}}, '{{{payload.name}}}', {{{payload.quantity}}})\n","outField":"payload","returnType":"0","throwErrors":1,"x":710,"y":1300,"wires":[["6f056873.aeef98"]]},{"id":"9ab55052.c844e","type":"inject","z":"a07b3581.696e08","name":"","topic":"","payload":"{\"id\": \"110\", \"name\": \"node-red-Maxine\", \"quantity\":\"1972\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":530,"y":1300,"wires":[["cc3ee9c6.db6208"]]},{"id":"6f056873.aeef98","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":890,"y":1300,"wires":[]},{"id":"9e54d0d8.2acf9","type":"split","z":"a07b3581.696e08","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":510,"y":300,"wires":[["9058bf96.e51c9","c536f869.093988"]]},{"id":"c536f869.093988","type":"MSSQL","z":"a07b3581.696e08","mssqlCN":"a51e405c.10f64","name":"SQL Server","query":"INSERT INTO \n [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) \nVALUES \n ('{{{payload.id}}}', '{{{payload.deviceId}}}', '{{{payload.label}}}', '{{{payload.message}}}', '{{{payload.serverity}}}', '{{{payload.activatedTime}}}', '{{{payload.clearedTime}}}')\n","outField":"payload","returnType":"1","throwErrors":1,"x":650,"y":380,"wires":[["e0cc2c9d.3f42b"]]},{"id":"9058bf96.e51c9","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":990,"y":300,"wires":[]},{"id":"e0cc2c9d.3f42b","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":970,"y":380,"wires":[]},{"id":"e41ba7c5.c117f8","type":"inject","z":"a07b3581.696e08","name":"fake the API","topic":"","payload":"[{\"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\"}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":160,"wires":[["9e54d0d8.2acf9"]]},{"id":"6e8900c8.83277","type":"catch","z":"a07b3581.696e08","name":"","scope":["c536f869.093988"],"uncaught":false,"x":650,"y":460,"wires":[["416168d0.936108"]]},{"id":"416168d0.936108","type":"debug","z":"a07b3581.696e08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":970,"y":460,"wires":[]},{"id":"a51e405c.10f64","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"Local SQL","server":"WIN-DGQMGP7MTKJ","port":"1433","encyption":false,"database":"db1","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false}]

```

---

<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 16:15 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/23 "2020-07-13T16:15:36Z")

</div>

you also have a typo in the MSSQL node

change `payload.serverity` to `payload.severity`

---

<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 16:42 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/24 "2020-07-13T16:42:06Z")

</div>

Hi Steve-Mcl, thank you...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/4/0460341310e88dad04d6be8e504c65f56045739f.png)

I actually had to put 2 x Split nodes to get it to work, great help and support - thank you!

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

---

<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:** [27 July 2020 16:42 UTC](https://discourse.nodered.org/t/insert-array-contents-into-mssql-table/30003/25 "2020-07-27T16:42:10Z")

</div>

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

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