# How to insert an array into mssql?

**URL:** <https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374>\
**Category:** General\
**Created:** [16 August 2019 06:39 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374 "2019-08-16T06:39:08Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 06:39 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/1 "2019-08-16T06:39:08Z")

</div>

DECLARE @val1 NVARCHAR(MAX)  
DECLARE @val2 NVARCHAR(MAX)  
DECLARE @myVal NVARCHAR(MAX)

SET @myVal={{{msg.payload}}};  
SET @val1= myVal[0];  
SET @val2= myVal[1];

INSERT INTO SPLT  
(  
VALUE1,  
VALUE2  
)  
VALUES  
(  
@val1,  
'value'  
)

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 06:52 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/2 "2019-08-16T06:52:41Z")

</div>

im getting some syntax error. anyone please help.

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [16 August 2019 06:55 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/3 "2019-08-16T06:55:55Z")

</div>

Hi Bilal. Please export your flow and add it here by using the `</>` button you see in the post editor. Next, add the error you are getting. We are unable to help you without any of these information.

Also please add your Node-RED version, NodeJS version and the name of the custom node for mssql you are using.

---

<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:** [16 August 2019 07:01 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/4 "2019-08-16T07:01:36Z")

</div>

My bet would be @val1 is empty OR its a string and you need `'` single quotes around `@val1`.

If you are using `node-red-contrib-mssql-plus` then attach a debug node after the MSSQL node (with the debug node set to output complete msg object`).

Then in the debug window, have a look at what is in `msg.query` (msg.query will contain the final SQL ran against the SQL server)

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:01 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/5 "2019-08-16T07:01:46Z")

</div>

Node red version : v0.20.7  
Node.js version: v10.16.2

Here is my nodes:

```auto
[{"id":"7888aca5.823a44","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"22aa6555.667b9a","type":"inject","z":"7888aca5.823a44","name":"","topic":"message","payload":"Bilal,Mustafa","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":180,"wires":[["b7873ce4.b2385"]]},{"id":"117859aa.6ce246","type":"debug","z":"7888aca5.823a44","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":550,"y":120,"wires":[]},{"id":"649a41e6.78c85","type":"MSSQL","z":"7888aca5.823a44","mssqlCN":"b63f32ce.fe4a3","name":"","query":"DECLARE @val1 NVARCHAR(MAX)\nDECLARE @val2 NVARCHAR(MAX)\nDECLARE @myVal NVARCHAR(MAX)\n\nSET @myVal='{{{payload}}}';\nSET @val1= myVal[0];\nSET @val2= myVal[1];\n\nINSERT INTO SPLT\n (\n VALUE1,\n VALUE2\n )\n VALUES\n (\n @val1,\n 'value'\n )\n","outField":"payload","x":350,"y":260,"wires":[["117859aa.6ce246"]]},{"id":"b7873ce4.b2385","type":"function","z":"7888aca5.823a44","name":"splitFun","func":"var myMsg= msg.payload;\nmsg.payload=myMsg.split(\",\");\nreturn msg;","outputs":1,"noerr":0,"x":300,"y":100,"wires":[["649a41e6.78c85","117859aa.6ce246"]]},{"id":"b63f32ce.fe4a3","type":"MSSQL-CN","z":"","name":"pme-57","server":"pme-57.cxzip7yq152r.ap-south-1.rds.amazonaws.com","encyption":false,"database":"test2"}]

```

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:05 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/6 "2019-08-16T07:05:50Z")

</div>

bro i'm splitting Bilal,Mustafa using split function. here is my function's code:

```auto
var myMsg= msg.payload;
msg.payload=myMsg.split(",");
return msg;

```

---

<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:** [16 August 2019 07:07 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/7 "2019-08-16T07:07:02Z")

</div>

in which case its a string and you need single quotes in your SQL values around the `@val1`.  
EDIT ^ ignore that

Again...

> If you are using `node-red-contrib-mssql-plus` then attach a debug node after the MSSQL node (with the debug node set to output complete msg object`). Then in the debug window, have a look at what is in `msg.query` (msg.query will contain the final SQL ran against the SQL server)

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:08 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/8 "2019-08-16T07:08:46Z")

</div>

same error again and again

RequestError: Incorrect syntax near '1'.

---

<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:** [16 August 2019 07:09 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/9 "2019-08-16T07:09:30Z")

</div>

are you using `node-red-contrib-mssql-plus`?

If so, do what I said and see what the final SQL is being sent to the SQL Server.

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:10 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/10 "2019-08-16T07:10:52Z")

</div>

bro i'm using node-red-contrib-mssql.

should i use node-red-contrib-mssql-plus???

---

<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:** [16 August 2019 07:11 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/11 "2019-08-16T07:11:16Z")

</div>

> [@BilalMustafa](#):
>
> SET @myVal={{{msg.payload}}};  
> SET @val1= myVal[0];  
> SET @val2= myVal[1];

ah man...  
you cant do that haha

try...

```auto
SET @val1= {{{msg.payload[0]}}};
SET @val2= {{{msg.payload[1]}}};

```

---

<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:** [16 August 2019 07:13 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/12 "2019-08-16T07:13:06Z")

</div>

yes. (IMHO) it is much more comprehensive and fixes some bugs like querying wrong SQL Server when more than one config & it has much more features like throwing errors, additional outputs (to help in situations like this) & more.

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:14 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/13 "2019-08-16T07:14:57Z")

</div>

getting a new error hahahah  
RequestError: Incorrect syntax near ';'.

here is my query:

```auto
DECLARE @val1 NVARCHAR(MAX)
DECLARE @val2 NVARCHAR(MAX)
--DECLARE @myVal NVARCHAR(MAX)

--SET @myVal={{{payload}}};
SET @val1= {{{msg.payload[0]}}};
SET @val2= {{{msg.payload[1]}}};

INSERT INTO SPLT
    (
        VALUE1,
        VALUE2
    )
    VALUES
    (
        @val1,
        @val2
    )

```

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:15 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/14 "2019-08-16T07:15:28Z")

</div>

that's great i'll use 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:** [16 August 2019 07:19 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/15 "2019-08-16T07:19:35Z")

</div>

are you certain `msg.payload` IS AN ARRAY?

put a debug node BEFORE the MSSQL to and inspect `msg.payload`

TBH, I never tried accessing `[array elements]` inside `{{{mustache}}}`.

If thats the issue then maybe add a change node BEFORE MSSQL to set `msg.val1` to msg.payload[0] and set `msg.val2` to msg.payload[1]

then use {{{msg.val1}}} in the SQL.

...

Install the mssql-plus node & debug the output - this will be far easier to fix.

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:24 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/16 "2019-08-16T07:24:46Z")

</div>

yes i have array in msg.payload.

---

<div class="post-metadata">

**Author:** ![BilalMustafa](https://avatars.discourse-cdn.com/v4/letter/b/c57346/32.png) [@BilalMustafa](https://discourse.nodered.org/u/BilalMustafa)\
**Post date:** [16 August 2019 07:36 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/17 "2019-08-16T07:36:50Z")

</div>

having same error....

anyone can help me or guide me a syntax for adding array into db.

---

<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:** [16 August 2019 07:43 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/18 "2019-08-16T07:43:04Z")

</div>

post a screen shot and copy/paste the text from the debug output of MSSQL-PLUS (debug node output set to full msg object)

In particular, I want to see msg.query

Be sure to set the debug node to show full object  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/599fa45d640af10bbbb412603071a04e5ce411ed.png)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [16 August 2019 07:55 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/19 "2019-08-16T07:55:29Z")

</div>

_Edited the title to something more descriptive._

---

<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:** [16 August 2019 08:52 UTC](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374/20 "2019-08-16T08:52:43Z")

</div>

> [@BilalMustafa](#):
>
> --SET @myVal={{{payload}}}; SET @val1= {{{msg.payload[0]}}}; SET @val2= {{{msg.payload[1]}}};

Actually, as the mustache is converted to text, you will need SQL compatable quotes around that part too...

```SQL
SET @val1= '{{{msg.payload[0]}}}';
SET @val2= '{{{msg.payload[1]}}}';

```

```auto

```

[Next page](https://discourse.nodered.org/t/how-to-insert-an-array-into-mssql/14374.md?page=2)