# UPDATED: node-red-contrib-mssql-plus 0.7.0 (beta) \*\*Bulk Insert support\*\*
**URL:** https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331
**Category:** Share Your Nodes
**Created:** [11 December 2020 02:10 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331 "2020-12-11T02:10:55Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [11 December 2020 02:10 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/1 "2020-12-11T02:10:55Z")
## UPDATE 2021/05/11 V0.7.0 now published to NPM / Flows library.
## Original thread below...
Hello all,
### Firstly - I would greatly appreciate beta testers and feedback
If anyone is dumping lots of data into a table, please give the beta a go & feedback.
This will allow me to be confident of merging these changes into the main branch and release.
#### About
I have now published ~~V0.7.0-beta.2~~ ~~V0.7.0-beta.3~~ **V0.7.0-beta.4** to NPM - [https://www.npmjs.com/package/node-red-contrib-mssql-plus/v/0.7.0-beta.4](https://www.npmjs.com/package/node-red-contrib-mssql-plus/v/0.7.0-beta.4)
This has numerous changes but most importantly, BULK INSERT mode is now implemented,
Also, the [MSSQL](https://www.npmjs.com/package/mssql) library is updated to `V7.0.0-alpha` (which importantly updates the tedious driver to v9 (tedious v9 has many improvements over v6.x that mssql v6.x currently depends on))
#### Install method...
- stop node-red
- open a cmd/terminal and `cd` to your node-red folder (often `cd ~/.node-red` )
- enter `npm i node-red-contrib-mssql-plus@0.7.0-beta.4`
- run node-red
#### Alternative method for latest version of node-red...
- Download the the [package tgz](https://registry.npmjs.org/node-red-contrib-mssql-plus/-/node-red-contrib-mssql-plus-0.7.0-beta.4.tgz)
- Open the pallet manger & install using the upload module button...
- restart node-red
#### Info
I have tested inserting 500000 in one go on an RPI3b with 1GB RAM.
I ran it over 200 times over the course of 2 days & memory did climb from 200MB peaking at 500MB but then settles at around 340MB - no leak (leaking memory is partly the reason I added bulk insert mode)
#### Changes...
- layout improvements
- add trustServerCertificate param
- overhaul promises and add bulk mode (using async/await)
- fix query typedInput width (layout improvement)
- improve built in docs
- spelling, comments, corrections
- Add new demo BULK
- update readme to reflect new version changes
- update TVP demo to use same CN object as bulk demo
- added table column options (readOnly, primary, identity, nullable) for bulk import
- minor version bump (beta) 0.7.0-beta.4
- update mssql to latest 7.0.0-beta.3
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 11:10 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/2 "2021-05-04T11:10:16Z")
Hello Steve,
Thanks for the great work.
Could you please provide an example of bulk insert query mode in the query editor?
Thanks in advance.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 11:14 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/3 "2021-05-04T11:14:17Z")
Hi.
Have you installed the latest beta.4?
Have you tried the built in demo?
CTRL+I
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 11:15 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/4 "2021-05-04T11:15:20Z")
NOTE: I would greatly appreciate your feedback on the bulk feature (good or bad)
Thanks.
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 11:32 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/5 "2021-05-04T11:32:00Z")
Sorry but after installing I can't find the examples:
I used the alternative install method.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 11:33 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/6 "2021-05-04T11:33:24Z")
> [@berzezo](#):
>
> Sorry but after installing I can't find the examples
Did you install the beta version?
Have you refreshed the browser?
Have you restarted node-red after updating to beta version?
what does palette manager show?
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 11:42 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/7 "2021-05-04T11:42:45Z")
Yes I installed beta version above:
I used the alternative method (download, and upload the package in the package manager).
Yes, I refreshed and restarted.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 11:54 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/8 "2021-05-04T11:54:07Z")
Odd, do you have other MSSQL contrib nodes installed?
Can you check in `~/.node-red/node_modules/node-red-contrib-mssql-plus/`
there should be an `examples` folder.
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 13:05 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/9 "2021-05-04T13:05:14Z")
I had the mssql nodes before, but I uninstalled as the documentation suggested.
There is no "node-red-contrib-mssql-plus/" folder here...
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 13:12 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/10 "2021-05-04T13:12:00Z")
Where is node-red installed?
Is this node-red on docker or a home assistant plugin?
Post the terminal output from node-red start up.
Also, please answer other question...
> [@Steve-Mcl](#):
>
> Odd, do you have other MSSQL contrib nodes installed?
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 13:33 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/11 "2021-05-04T13:33:44Z")
I had the mssql nodes before, but I uninstalled as the documentation suggested before I installed the mssql-plus.
Node-red is running on windows 10.
Terminal output:
```auto
> Welcome to Node-RED
> ===================
>
> 4 May 15:05:37 - [info] Node-RED version: v1.2.7
> 4 May 15:05:37 - [info] Node.js version: v14.15.4
> 4 May 15:05:37 - [info] Windows_NT 10.0.17763 x64 LE
> 4 May 15:05:37 - [info] Loading palette nodes
> 4 May 15:05:38 - [info] Dashboard version 2.28.1 started at /ui
> 4 May 15:05:38 - [info] Settings file : C:\Users\zoltan.berzsenyi\.node-red\settings.js
> 4 May 15:05:38 - [info] Context store : 'default' [module=memory]
> 4 May 15:05:38 - [info] User directory : \Users\zoltan.berzsenyi\.node-red
> 4 May 15:05:38 - [warn] Projects disabled : editorTheme.projects.enabled=false
> 4 May 15:05:38 - [info] Flows file : \Users\zoltan.berzsenyi\.node-red\flows_HU-VE-C0066.json
> 4 May 15:05:38 - [info] Server now running at http://127.0.0.1:1880/
> 4 May 15:05:38 - [warn]
>
> ---------------------------------------------------------------------
> Your flow credentials file is encrypted using a system-generated key.
>
> If the system-generated key is lost for any reason, your credentials
> file will not be recoverable, you will have to delete it and re-enter
> your credentials.
>
> You should set your own key using the 'credentialSecret' option in
> your settings file. Node-RED will then re-encrypt your credentials
> file using your chosen key the next time you deploy a change.
> ---------------------------------------------------------------------
>
> 4 May 15:05:38 - [info] Starting flows
> 4 May 15:05:40 - [info] Started flows
```
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 13:40 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/12 "2021-05-04T13:40:23Z")
> [@berzezo](#):
>
> `[info] User directory : \Users\zoltan.berzsenyi\.node-red`
So, look in `\Users\zoltan.berzsenyi\.node-red\node_modules\node-red-contrib-mssql-plus\` is there an examples folder in there?
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 13:48 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/13 "2021-05-04T13:48:25Z")
There is no such a folder (node-red-contrib-mssql-plus)
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 13:57 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/14 "2021-05-04T13:57:39Z")
Hmm, this must be an issue due to using the upload tgz method. I'll check it out later.
In the mean time, the demo is [here](https://raw.githubusercontent.com/bestlong/node-red-contrib-mssql-plus/mssql7/examples/Bulk%20demo.json)
copy all text and import using CTRL+I
Please remember to feedback on your experiences.
Ta.
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 14:04 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/15 "2021-05-04T14:04:34Z")
Thanks for your help.
I'll give a feedback as I can test it.
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [4 May 2021 14:19 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/16 "2021-05-04T14:19:45Z")
I have a syntax error for this flow:
Columns:
```auto
{"MachineNo":"MachineNo","MacType":"MacType","MachineName":"MachineName","Date":"Date","PowerOn":"PowerOn","AlarmState":"AlarmState","LimitState":"LimitState","ProgramStopState":"ProgramStopState","FeedHoldState":"FeedHoldState","STMState":"STMState","RunState":"RunState","CuttingState":"CuttingState","SetUp":"SetUp","NoOperator":"NoOperator","Wait":"Wait","Maintenance":"Maintenance","Other":"Other","ManualMode":"ManualMode","MDIMode":"MDIMode","AutoMode":"AutoMode","MacManMode":"MacManMode","ToolSetMode":"ToolSetMode","OriginSetMode":"OriginSetMode","ParameterSetMode":"ParameterSetMode","ProgramOpeMode":"ProgramOpeMode","ProgramFileName":"ProgramFileName","AlarmLevel":"AlarmLevel","AlarmNo":"AlarmNo","AlarmCode":"AlarmCode","AlarmString":"AlarmString","AlarmMessage":"AlarmMessage","AlarmObject":"AlarmObject","PowerOnTime":"PowerOnTime","AlarmStateTime":"AlarmStateTime","AlarmRunStateTime":"AlarmRunStateTime","LimitStateTime":"LimitStateTime","ProgramStopStateTime":"ProgramStopStateTime","FeedHoldStateTime":"FeedHoldStateTime","STMStateTime":"STMStateTime","RunStateTime":"RunStateTime","CuttingTime":"CuttingTime","SpindleCMDTime":"SpindleCMDTime","TotalActivePower":"TotalActivePower","SetUpTime":"SetUpTime","NoOperatorTime":"NoOperatorTime","WaitTime":"WaitTime","MaintenanceTime":"MaintenanceTime","OtherTime":"OtherTime","ManualModeTime":"ManualModeTime","MDIModeTime":"MDIModeTime","AutoModeTime":"AutoModeTime","MacManModeTime":"MacManModeTime","ToolSetModeTime":"ToolSetModeTime","OriginSetModeTime":"OriginSetModeTime","ParameterSetModeTime":"ParameterSetModeTime","ProgramOpeModeTime":"ProgramOpeModeTime","FeedOverride":"FeedOverride","SpindleOverride":"SpindleOverride"}
```
Rows (msg.payload):
```auto
[{"MachineNo":"1","MacType":"0","MachineName":"LU45II","Date":"2021/03/29 15:08:00","PowerOn":"1","AlarmState":"0","LimitState":"1","ProgramStopState":"0","FeedHoldState":"0","STMState":"0","RunState":"1","CuttingState":"0","SetUp":"0","NoOperator":"0","Wait":"0","Maintenance":"0","Other":"0","ManualMode":"0","MDIMode":"0","AutoMode":"1","MacManMode":"0","ToolSetMode":"0","OriginSetMode":"1","ParameterSetMode":"0","ProgramOpeMode":"0","ProgramFileName":"R-41654-FO.MIN","AlarmLevel":"0","AlarmNo":"0","AlarmObject":"0","PowerOnTime":"60000","AlarmStateTime":"0","AlarmRunStateTime":"0","LimitStateTime":"60000","ProgramStopStateTime":"0","FeedHoldStateTime":"0","STMStateTime":"0","RunStateTime":"60000","CuttingTime":"0","SpindleCMDTime":"60000","TotalActivePower":"0","SetUpTime":"0","NoOperatorTime":"0","WaitTime":"0","MaintenanceTime":"0","OtherTime":"0","ManualModeTime":"0","MDIModeTime":"0","AutoModeTime":"60000","MacManModeTime":"0","ToolSetModeTime":"0","OriginSetModeTime":"60000","ParameterSetModeTime":"0","ProgramOpeModeTime":"0","FeedOverride":"0","SpindleOverride":"100"},{"MachineNo":"1","MacType":"0","MachineName":"LU45II","Date":"2021/03/29 15:09:00","PowerOn":"1","AlarmState":"0","LimitState":"1","ProgramStopState":"0","FeedHoldState":"0","STMState":"0","RunState":"1","CuttingState":"0","SetUp":"0","NoOperator":"0","Wait":"0","Maintenance":"0","Other":"0","ManualMode":"0","MDIMode":"0","AutoMode":"1","MacManMode":"0","ToolSetMode":"0","OriginSetMode":"1","ParameterSetMode":"0","ProgramOpeMode":"0","ProgramFileName":"R-41654-FO.MIN","AlarmLevel":"0","AlarmNo":"0","AlarmObject":"0","PowerOnTime":"60000","AlarmStateTime":"0","AlarmRunStateTime":"0","LimitStateTime":"60000","ProgramStopStateTime":"0","FeedHoldStateTime":"0","STMStateTime":"0","RunStateTime":"60000","CuttingTime":"0","SpindleCMDTime":"60000","TotalActivePower":"0","SetUpTime":"0","NoOperatorTime":"0","WaitTime":"0","MaintenanceTime":"0","OtherTime":"0","ManualModeTime":"0","MDIModeTime":"0","AutoModeTime":"60000","MacManModeTime":"0","ToolSetModeTime":"0","OriginSetModeTime":"60000","ParameterSetModeTime":"0","ProgramOpeModeTime":"0","FeedOverride":"0","SpindleOverride":"100"}]
```
Thanks in advance.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 17:11 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/17 "2021-05-04T17:11:57Z")
Could you share your table as a `CREATE` script and I'll test it.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [4 May 2021 17:29 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/18 "2021-05-04T17:29:56Z")
Actually, i suspect that this is due to there are more columns than data items.
If you have no value for certain columns, add them with a value of null
e.g. if your columns were
```auto
{
"MachineNo": "MachineNo",
"MacType": "MacType",
"MachineName": "MachineName"
}
```
and supply the data...
```auto
[
{
"MachineNo": "1",
"MacType": "0",
"MachineName": "LU45II"
},
{
"MachineNo": "1",
"MacType": "0"
}
]
```
i think you will get this (unhelpful) error.
instead, if you have no value for MachineName, send null...
```auto
[
{
"MachineNo": "1",
"MacType": "0",
"MachineName": "LU45II"
},
{
"MachineNo": "1",
"MacType": "0",
"MachineName": null
}
]
```
---
### Author:  [@berzezo](https://discourse.nodered.org/u/berzezo)
#### Post date: [6 May 2021 16:59 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/19 "2021-05-06T16:59:28Z")
Thanks for the suggestions.
In the meantime I managed to pass something to SQL database, but all the values are NULLs.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [6 May 2021 17:18 UTC](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331/20 "2021-05-06T17:18:40Z")
Can you share your table as a create script, a sample of data (10 rows will do) & flow (the SQL node will do - use ctrl+e to export)
Remember to paste code/data into the reply between backticks
SQL...
`````
`crate table ....`
`````
Data sample...
`````
`[{"machine": "1", ...`
`````
Flow...
`````
`[{"id": "aba1àc:64f98", ...`
`````
[Next page](https://discourse.nodered.org/t/updated-node-red-contrib-mssql-plus-0-7-0-beta-bulk-insert-support/37331.md?page=2)