# MSSQL-Plus - Bulk Insert - use msg.QueryParams how?
**URL:** https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491
**Category:** General
**Created:** [16 July 2021 13:00 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491 "2021-07-16T13:00:30Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@IoTPlay](https://discourse.nodered.org/u/IoTPlay)
#### Post date: [16 July 2021 13:00 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/1 "2021-07-16T13:00:30Z")
@Steve-Mcl - I do want to try to use the Bulk insert - (which is working like a dream, thank you !), but not using the "Parameters Editor" - but by pointing `msg.queryParams` at it. Could you show me an example of what that would look like? If its on the node docs, I apologise, no finding it.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [16 July 2021 13:37 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/2 "2021-07-16T13:37:08Z")
Have you tried the built in demo?
---
### Author:  [@IoTPlay](https://discourse.nodered.org/u/IoTPlay)
#### Post date: [16 July 2021 14:31 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/3 "2021-07-16T14:31:25Z")
Steve, yes - I got it working the way it shows on screen, but from reading the help, it looks like their is a way to - for the columns - not use the `Editor`, but rather - on the drop-down of Columns, choose `msg.QueryParams` - then push into the msg.queryparams the same info per the Editor - but from a message - I cannot find what such syntax would look like?
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [16 July 2021 14:44 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/4 "2021-07-16T14:44:30Z")
The built in help shows you..
However it is not a full example. If you attach a debug (set for full message) to the demo node you will see the full format of the parameter object...
.. but it needs cleaning up before use, like this ...
```auto
msg.queryParams = [
{
"output": false,
"name": "IP",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Name",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Timestamp",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Param_Name",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}, {
"output": false,
"name": "Param_Value",
"type": "NVarChar(200)",
"options": {
"nullable": true,
"primary": false,
"identity": false,
"readOnly": false
}
}
];
return msg;
```
Here is the same demo using `msg.queryParams`
```auto
[{"id":"7aea13edb79ff608","type":"inject","z":"1683bd9a.5e0a02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"rows","payload":"1000","payloadType":"num","x":1360,"y":1200,"wires":[["ca0236291516ce3b"]]},{"id":"ca0236291516ce3b","type":"function","z":"1683bd9a.5e0a02","name":"generate data","func":"// Table fields...\n// IP, Name, Timestamp, Param_Name, Param_Value\n\nlet IP = \"1.2.3.4\";\nlet Name = \"fake_device\";\nlet now = (new Date()).toISOString(); //generate a timestamp\nmsg.bulk_data_to_insert = []; //the msg property we will use to pass the data array to the MSSQL node\n\n//generate fake data rows of data in the following format...\n/*\n[\n [col1, col2, coln...], //row 1\n [col1, col2, coln...], //row 2\n [col1, col2, coln...], //row n\n]\n*/\nif (msg.topic == \"rows\") {\n for (let x = 1; x <= msg.payload; x++) {\n msg.bulk_data_to_insert.push([\n IP, Name, now, `P${x + 1000}`, Math.random()\n]);\n }\n} else {\n for (let x = 1; x <= msg.payload; x++) {\n msg.bulk_data_to_insert.push({\n IP: IP, \n Name: Name, \n Timestamp: now, \n Param_Name: `P${x + 1000}`,\n Param_Value: Math.random()\n });\n }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1580,"y":1200,"wires":[["d1f0832aa0fe0553"]]},{"id":"d8f9d170679713bd","type":"debug","z":"1683bd9a.5e0a02","name":"Bulk Data to insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"bulk_data_to_insert","targetType":"msg","statusVal":"","statusType":"auto","x":2010,"y":1200,"wires":[]},{"id":"9be796c66a852efd","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"bulk insert -> demo_device_parameters_table","outField":"payload","returnType":"0","throwErrors":1,"query":"demo_device_parameters_table","modeOpt":"","modeOptType":"bulk","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"bulk_data_to_insert","rowsType":"msg","params":[],"x":1720,"y":1240,"wires":[["12a579dd8898e035"]]},{"id":"12a579dd8898e035","type":"debug","z":"1683bd9a.5e0a02","name":"Bulk Insert Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1890,"y":1280,"wires":[]},{"id":"6fdb4dcd8b97e9f4","type":"inject","z":"1683bd9a.5e0a02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"rows","payload":"10000","payloadType":"num","x":1370,"y":1240,"wires":[["ca0236291516ce3b"]]},{"id":"110f5a0c8051eaab","type":"inject","z":"1683bd9a.5e0a02","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"objects","payload":"2000","payloadType":"num","x":1370,"y":1280,"wires":[["ca0236291516ce3b"]]},{"id":"53cfe74263fe056e","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"Create demo_device_parameters_table","outField":"payload","returnType":"1","throwErrors":1,"query":"\r\nSET ANSI_NULLS ON\r\nSET QUOTED_IDENTIFIER ON\r\n\r\nCREATE TABLE [dbo].[demo_device_parameters_table](\r\n [ID] int not null identity(1,1),\t\r\n [IP] nvarchar(200) NULL,\r\n [Name] nvarchar(200) NULL,\r\n [Timestamp] nvarchar(200) NULL,\r\n [Param_Name] nvarchar(200) NULL,\r\n [Param_Value] float NULL\r\n) ON [PRIMARY]","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":1600,"y":1120,"wires":[["a46ed483d0141f3a"]]},{"id":"26274d41cfe53f24","type":"inject","z":"1683bd9a.5e0a02","name":"Start","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":1350,"y":1120,"wires":[["53cfe74263fe056e"]]},{"id":"a46ed483d0141f3a","type":"debug","z":"1683bd9a.5e0a02","name":"Table creation","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1880,"y":1120,"wires":[]},{"id":"7e2fd2b76e5faa86","type":"comment","z":"1683bd9a.5e0a02","name":"1. Open the MSSQL node and set up the connection to your SQL Server","info":"","x":1530,"y":900,"wires":[]},{"id":"0d365aa11edfeae6","type":"comment","z":"1683bd9a.5e0a02","name":"2. Click the \"Start\" Inject to create the table","info":"","x":1440,"y":940,"wires":[]},{"id":"52923bffa155a6f9","type":"comment","z":"1683bd9a.5e0a02","name":"3. Click any of the \"rows: xxxx\" Injects to perform a Bulk Insert","info":"","x":1500,"y":980,"wires":[]},{"id":"7e8b55a14d84ac04","type":"comment","z":"1683bd9a.5e0a02","name":"4. Click \"Get Data\" Inject to select data from the table","info":"","x":1470,"y":1020,"wires":[]},{"id":"516770ee011ea9fd","type":"inject","z":"1683bd9a.5e0a02","name":"Get top 1000 with ID >= 200","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"1000","payload":"200","payloadType":"num","x":1420,"y":1360,"wires":[["bef9b49212f0cdd8"]]},{"id":"bef9b49212f0cdd8","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"Select Data","outField":"payload","returnType":"0","throwErrors":1,"query":"PRINT 'Selecting TOP {{{topic}}} from demo_device_parameters_table'\r\nPRINT 'Where ID if > or = to '\r\nPRINT @ID\r\nSELECT TOP {{{topic}}} * \r\nFROM [dbo].[demo_device_parameters_table]\r\nWHERE ID >= @ID\r\nPRINT @@ROWCOUNT\r\n\r\n--Print info will appear in msg.sqlInfo","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"ID","type":"int","valueType":"msg","value":"payload"}],"x":1670,"y":1380,"wires":[["bf168e2cc2a4c4e9"]]},{"id":"bf168e2cc2a4c4e9","type":"debug","z":"1683bd9a.5e0a02","name":"Selected Data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1880,"y":1380,"wires":[]},{"id":"07f20cc163b440ba","type":"inject","z":"1683bd9a.5e0a02","name":"Clean up","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":1360,"y":1480,"wires":[["fe3226e2bdbee79a"]]},{"id":"fe3226e2bdbee79a","type":"MSSQL","z":"1683bd9a.5e0a02","mssqlCN":"8d9d212a.cea03","name":"Drop demo_device_parameters_table","outField":"payload","returnType":"1","throwErrors":1,"query":"\r\nDROP TABLE [dbo].[demo_device_parameters_table]\r\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":1610,"y":1480,"wires":[["b5cd717fe420b6c0"]]},{"id":"b5cd717fe420b6c0","type":"debug","z":"1683bd9a.5e0a02","name":"Clean up result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1880,"y":1480,"wires":[]},{"id":"ae2feb21292bb5c9","type":"inject","z":"1683bd9a.5e0a02","name":"Get top 50 with ID >= 1000","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"50","payload":"1000","payloadType":"num","x":1410,"y":1400,"wires":[["bef9b49212f0cdd8"]]},{"id":"399d5de29539a4ac","type":"comment","z":"1683bd9a.5e0a02","name":"5. Click \"Clean up\" Inject to drop the table","info":"","x":1440,"y":1060,"wires":[]},{"id":"d1f0832aa0fe0553","type":"function","z":"1683bd9a.5e0a02","name":"set queryParams","func":"msg.queryParams = [\n {\n \"output\": false,\n \"name\": \"IP\",\n \"type\": \"NVarChar(200)\",\n \"valueType\": \"msg\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Name\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Timestamp\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Param_Name\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }, {\n \"output\": false,\n \"name\": \"Param_Value\",\n \"type\": \"NVarChar(200)\",\n \"options\": {\n \"nullable\": true,\n \"primary\": false,\n \"identity\": false,\n \"readOnly\": false\n }\n }\n];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1790,"y":1200,"wires":[["d8f9d170679713bd","9be796c66a852efd"]]},{"id":"8d9d212a.cea03","type":"MSSQL-CN","tdsVersion":"7_4","name":"My DB","server":"192.168.1.38","port":"1433","encyption":false,"trustServerCertificate":true,"database":"","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
```
---
### Author:  [@IoTPlay](https://discourse.nodered.org/u/IoTPlay)
#### Post date: [16 July 2021 21:30 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/5 "2021-07-16T21:30:27Z")
Thank you! Powerful !!
---
### Author:  [@Golonder](https://discourse.nodered.org/u/Golonder)
#### Post date: [21 July 2021 12:17 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/6 "2021-07-21T12:17:03Z")
Hi Steve-Mcl,
I tried to reproduce your example but unfortunately I was failing several times.
Creating the table works fine, but inserting the data using Bulk Insert does not work !
I am doing nothing else than just executing your example step by step.
But there seems to be any kind of formatting error.
Could you please have a look - this is preventing me from finishing a big project ☹
BR
G
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [21 July 2021 14:34 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/7 "2021-07-21T14:34:33Z")
I think there is a bug in underlying tedious library recently introduced that is affecting many downstream libraries (including this one).
I will look at how to get it working later this evening and let you know.
---
### Author:  [@Golonder](https://discourse.nodered.org/u/Golonder)
#### Post date: [21 July 2021 16:21 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/8 "2021-07-21T16:21:02Z")
That would be great 🙂
As I am using this library as a part of my daily business in the office I would appreciate to honor your work with a few coins from the company purse 🙂 Is there a way to "thank you" in an official way for making life a bit easier for us ?
BR
G
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [23 July 2021 11:19 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/9 "2021-07-23T11:19:42Z")
As of last night, the underlying lib (tedious) that caused this issue was published (V11.4.0) and any new install of `node-red-contrib-mssql-plus` will get the updated packages.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [6 August 2021 11:20 UTC](https://discourse.nodered.org/t/mssql-plus-bulk-insert-use-msg-queryparams-how/48491/10 "2021-08-06T11:20:37Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.