How to Insert Table Values Into MS SQL and MySQL as Separate Records

I think Philip is better suited to answer this than I am since he made mine work. But if we don’t hear from him I’m happy to post mine. The only thing is mine is for MS SQL not MySQL. Having said that, the difference is probably only syntax.

Have you tried separating each of your statements with a semicolon and sending that to your SQL node?

Yes I have. Still did not work.

I checked the source for node-red-node-mysql and it looks to have multiple statements queries enabled, so it should work - can you post the sql that you are sending to the node?

1 Like

msg.topic = “insert into Zone1 (Temp, CO2 ,RH) values (” +
msg.payload.tCurTemp0 + ", " + msg.payload.tCurHumidity0 + ", " +
msg.payload.tCurCO20 + “)” + “insert into Zone2 (Temp, CO2 ,RH) values (”

  • msg.payload.tCurTemp1 + ", " + msg.payload.tCurHumidity1 + ", " +
    msg.payload.tCurCO21 + “)”
  • “insert into Zone3 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp2 +
    ", " + msg.payload.tCurHumidity2 + ", " + msg.payload.tCurCO22 + “)”
  • “insert into Zone4 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp3 +
    ", " + msg.payload.tCurHumidity3 + ", " + msg.payload.tCurCO23 + “)”
  • “insert into Zone5 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp4 +
    ", " + msg.payload.tCurHumidity4 + ", " + msg.payload.tCurCO24 + “)”
  • “insert into Zone6 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp5 +
    ", " + msg.payload.tCurHumidity5 + ", " + msg.payload.tCurCO25 + “)”
  • “insert into Zone7 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp6 +
    ", " + msg.payload.tCurHumidity6 + ", " + msg.payload.tCurCO26 + “)”
  • “insert into Zone8 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp7 +
    ", " + msg.payload.tCurHumidity7 + ", " + msg.payload.tCurCO27 + “)”
  • “insert into Zone9 (Temp, CO2 ,RH) values (” + msg.payload.tCurTemp8 +
    ", " + msg.payload.tCurHumidity8 + ", " + msg.payload.tCurCO28 + “)”;

return msg;

I’m not seeing any semicolons being put in after each insert statement.

Strong recommendation: Consider changing your database design/schema to have a single table with the zone # as a column. It will make life much easier for you when querying your data.

Yeah I took them out to try it without them after I tried using them. I dont have the option to set them as just separate columns because there already are multiple columns in each table. If you mean by adding the Name of the Zone to each column etc, that will not work either. Our customers want it this specific way.

You will need the semicolons. Did the mysql node generate an error that would help track down the issue? Can you post the generated sql (after this function) for us to look at?

I don’t understand what you are saying here. Name of the zone on each column is not what I am saying. This is what I am saying:

insert into Zone (Zone, Temp, CO2, RH) values (1, 72, 500, 55);

One table for all the zones. When your customer adds zone 324 and you need the data:

select * form Zone where Zone=324;

No additional table needed. If the customer insists on a “zone table” then create a view:

create view Zone1 as
select * form Zone where Zone=1; --List the columns out, don't use the * when creating a view though.

What you have now is a bad design that will cause more trouble down the road. Imagine what you would have to do if your customer requested a report of all the zone temperatures from yesterday. You will need to write a separate query for each zone. If you do it the right way, then this is simple:

select Zone, Temp from Zone where EntryDate = '2/14/2018'; --If you have a EntryDate column

1 Like

Hello,
Following up on this thread. I have almos the same situation in that I have 2 arrays coming from PAC and I need these arrays to be inserted into a MSSQL DB. The instructions provided between phillip and Born2see works fine for just one array, but how would the process be for 2 arrays doing a multi row insert to SQL?

Are the two arrays from the PAC going into different columns of the same table?

Here is an example of inserting two arrays into different columns of the same table. It is assumed the arrays are the same length. You can add additional arrays/columns as needed.

The sequence is:

  1. Get all the array data from the PAC (I simulate this with the “Test Data” inject node). *
  2. Transform the data into a new array of javascript objects that represent all our data. If you have other data you want to include with your insert, this is the place to add it.
  3. Split the array so we can build a SQL statement.
  4. Build our value statement portion of our insert using the data in the javascript object we created.
  5. Join our value statements together.
  6. Append our value statements into a full SQL insert statement.
  7. Send the insert to the database - This insert is setup for MSSQL, you may need to adjust the syntax for other DBs.

*If inserting string data from the PAC that could come from the end user, then look up and understand what an SQL injection attack is. Also, your string data will need surrounded by ‘apostrophes’ in the “Build SQL value list” node.

Flow:

[{"id":"26198e86.a487aa","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"true","x":493,"y":212,"wires":[]},{"id":"382a8fd4.ead828","type":"function","z":"8d6f74c8.a53ad8","name":"Move payload to msg","func":"msg = msg.payload;\nmsg.payload = 1517425818148;\n\nreturn msg;","outputs":1,"noerr":0,"x":334,"y":263,"wires":[["26198e86.a487aa","2f590c9b.a5dbac"]]},{"id":"68ef6875.56e55","type":"function","z":"8d6f74c8.a53ad8","name":"Transform Data","func":"var data = [];\n\nfor (var i = 0; i< msg.ntColumn1.length; i++)\n{\n\tdata.push({\n\t\t\t\"Column1\" : msg.ntColumn1[i],\n\t\t\t\"Column2\" : msg.ntColumn2[i]\n\t\t\t});\n}\n\nmsg.payload = data;\n\nreturn msg;","outputs":"1","noerr":0,"x":197,"y":379,"wires":[["14256041.446a68","be85238.d0afc6"]]},{"id":"14256041.446a68","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"false","x":360,"y":332,"wires":[]},{"id":"66e195fb.73e5cc","type":"split","z":"8d6f74c8.a53ad8","name":"","splt":"\\n","x":156,"y":489,"wires":[["34eb0535.813e7a"]]},{"id":"34eb0535.813e7a","type":"template","z":"8d6f74c8.a53ad8","name":"Build SQL value list","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"({{payload.Column1}},{{{payload.Column2}}})\n","x":321,"y":489.3333740234375,"wires":[["c726ab36.6e99d8"]]},{"id":"c726ab36.6e99d8","type":"join","z":"8d6f74c8.a53ad8","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":",","timeout":"","count":"","x":483,"y":489.66668701171875,"wires":[["f6792a0e.6539c8"]]},{"id":"f6792a0e.6539c8","type":"template","z":"8d6f74c8.a53ad8","name":"Create SQL Statement","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"insert into MyTable ([Column1], [Column2]) values \n{{{payload}}};","x":655.9999389648438,"y":490.16668701171875,"wires":[["d5079ceb.68efb8"]]},{"id":"d5079ceb.68efb8","type":"debug","z":"8d6f74c8.a53ad8","name":"Send to your DB","active":true,"console":"false","complete":"payload","x":703,"y":424,"wires":[]},{"id":"c097c.18fc7685","type":"inject","z":"8d6f74c8.a53ad8","name":"Test Data","topic":"","payload":"{\"ntColumn1\":[0,1,2,3,4,5,6,7,8,9],\"ntColumn2\":[0,10,20,30,40,50,60,70,80,90]}","payloadType":"json","repeat":"","crontab":"","once":false,"x":141,"y":263,"wires":[["382a8fd4.ead828"]]},{"id":"f50a54ff.bc78","type":"comment","z":"8d6f74c8.a53ad8","name":"Connect wire to enable","info":"","x":212,"y":109,"wires":[]},{"id":"d9609ba2.832488","type":"pac-read","z":"8d6f74c8.a53ad8","device":"93a5787a.9451b","dataType":"int32-table","tagName":"ntColumn1","tableStartIndex":"0","tableLength":"9","value":"ntColumn1","valueType":"msg","topic":"","topicType":"none","name":"ntColumn1","x":312,"y":152,"wires":[["fce3eb43.36301","2774f2b9.d0509e"]]},{"id":"fce3eb43.36301","type":"pac-read","z":"8d6f74c8.a53ad8","device":"93a5787a.9451b","dataType":"int32-table","tagName":"ntColumn2","tableStartIndex":"0","tableLength":"9","value":"ntColumn2","valueType":"msg","topic":"","topicType":"none","name":"ntColumn2","x":516,"y":152,"wires":[["5b4249b1.7047c8","118cf47d.aaf4f4"]]},{"id":"fe77aca6.4644a8","type":"inject","z":"8d6f74c8.a53ad8","name":"Timer Inject","topic":"","payload":"[]","payloadType":"json","repeat":"30","crontab":"","once":true,"x":149,"y":152,"wires":[[]]},{"id":"914aae04.2dc878","type":"comment","z":"8d6f74c8.a53ad8","name":"Get data from strategy","info":"Note that the End Time table contains\none extra element that is used for \nsyncronization with the strategy to make \nsure the strategy is done writing to \nthe table.","x":115,"y":64,"wires":[]},{"id":"118cf47d.aaf4f4","type":"link out","z":"8d6f74c8.a53ad8","name":"To Data Process","links":["1b32fb1f.77678d"],"x":656,"y":152,"wires":[]},{"id":"7d2208ac.08d5b8","type":"comment","z":"8d6f74c8.a53ad8","name":"Debug/Test injections","info":"","x":110,"y":207,"wires":[]},{"id":"2f590c9b.a5dbac","type":"link out","z":"8d6f74c8.a53ad8","name":"To Data Process","links":["1b32fb1f.77678d"],"x":518,"y":262,"wires":[]},{"id":"1b32fb1f.77678d","type":"link in","z":"8d6f74c8.a53ad8","name":"Data Process","links":["2f590c9b.a5dbac","118cf47d.aaf4f4"],"x":63,"y":379,"wires":[["68ef6875.56e55"]]},{"id":"b5602f0f.29bcf8","type":"comment","z":"8d6f74c8.a53ad8","name":"Process Data","info":"","x":80,"y":330,"wires":[]},{"id":"2c6945d8.b3bfd2","type":"link in","z":"8d6f74c8.a53ad8","name":"Process for SQL Server","links":["be85238.d0afc6"],"x":62,"y":488,"wires":[["66e195fb.73e5cc"]]},{"id":"be85238.d0afc6","type":"link out","z":"8d6f74c8.a53ad8","name":"To Process for SQL Server","links":["2c6945d8.b3bfd2"],"x":349,"y":379,"wires":[]},{"id":"c79f316.628cdd","type":"comment","z":"8d6f74c8.a53ad8","name":"Build SQL Statement and send","info":"","x":140,"y":431,"wires":[]},{"id":"2774f2b9.d0509e","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"true","x":373,"y":61,"wires":[]},{"id":"5b4249b1.7047c8","type":"debug","z":"8d6f74c8.a53ad8","name":"","active":false,"console":"false","complete":"true","x":538,"y":62,"wires":[]},{"id":"93a5787a.9451b","type":"pac-device","z":"","address":"192.168.0.100","protocol":"http"}]
1 Like

Yes, the two arrays are of the same length and going to the same table.
I will try out the code that you provided and reply soon

Hello there again, I have successfully implemented the flow and adapted it to my application.

Thank you again for the support!