# Insert new record into MSSQL

**URL:** <https://discourse.nodered.org/t/insert-new-record-into-mssql/73823>\
**Category:** General\
**Created:** [17 January 2023 17:53 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823 "2023-01-17T17:53:46Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dpenz](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@dpenz](https://discourse.nodered.org/u/dpenz)\
**Post date:** [17 January 2023 17:53 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823/1 "2023-01-17T17:53:46Z")

</div>

I would appreciate help with inserting a data record into a MSSQL database table. I am using the MSSQL-PLUS node. My flow obtains SQL column name and data (could be string or floating number) from an Excel worksheet. This part works fine. Also the MSSQL-PLUS node works fine to connect to the database. I have been struggling with the syntax of the INSERT statement, to get the payload into just the right form for MSSQL to accept it. Here is the flow:

 ![SQL flow 1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/3/f3152751faa7d4b662dbb1a1f6256badd9faa2cb.jpeg)

And here is the debug information:

 ![SQL debug 1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/d/2d6f086fa6ba60beb18f8b993cb8954b31bb24e2.jpeg)

The SQL statement (as seen by SQL server) should look something like this:

INSERT INTO test\_data.settings (test\_run#, pump\_type, [etc] ) VALUES ( "00002189", "13", ... 240, 5, 208, [etc] )

The string values should have quotes and the numbers no quotes. The field names should not have quotes. Can the MSSQL node handle input in the present form or does the input stream need further conditioning, and what should the INSERT statement look like?

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:** [17 January 2023 18:32 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823/2 "2023-01-17T18:32:23Z")

</div>

> [@dpenz](#):
>
> and what should the INSERT statement look like

It should look exactly like you want it.

Example..

```sql
INSERT INTO test_data.settings 
( [test_run#], [pump_type], [etc] ) 
VALUES ( 
  @param1, 
  @param2, 
  @param3
)

```

where @param1 ~ @param3 are entered in the "parameters" section & mapped to the correct property in the `msg` and set for the intended type.

Here is a couple of threads that explain how:

- [Logging data in to MSSQL - #13 by Steve-Mcl](https://discourse.nodered.org/t/logging-data-in-to-mssql/67539/13)
- [SQL data return array data types? MSSQL Node - #3 by Steve-Mcl](https://discourse.nodered.org/t/sql-data-return-array-data-types-mssql-node/72157/3)
- [and more](https://discourse.nodered.org/search?q=%40Steve-Mcl%20mssql%20parameters%20order%3Alatest)

---

<div class="post-metadata">

**Author:** ![dpenz](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@dpenz](https://discourse.nodered.org/u/dpenz)\
**Post date:** [17 January 2023 19:06 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823/3 "2023-01-17T19:06:18Z")

</div>

Thanks for this direction, Steve. I’ll have another go and report back. dp

---

<div class="post-metadata">

**Author:** ![dpenz](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@dpenz](https://discourse.nodered.org/u/dpenz)\
**Post date:** [17 January 2023 20:24 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823/4 "2023-01-17T20:24:32Z")

</div>

Steve,

I made a trial-size SQL table, set up the flow as you suggested, and now it works great!

 ![SQL flow 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/3/032eb96ccd2283cc5417c16369813cd9cde5ca94.jpeg)

Is it the case that the field names must be "hard-wired" into the INSERT statement, as is done here? If so, there is no need for me to generate that part of the input stream. The input stream would only need to have data.

Dave

---

<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:** [17 January 2023 20:59 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823/5 "2023-01-17T20:59:02Z")

</div>

They don't have to be hard coded. You can generate SQL inserts in a function but you will lose the security parameters provide.

As for transforming your data into arrays - no, not necessary. If the data is an object to start with, this becomes much easier.

---

<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:** [18 March 2023 20:59 UTC](https://discourse.nodered.org/t/insert-new-record-into-mssql/73823/6 "2023-03-18T20:59:05Z")

</div>

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