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


#25

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.


#26

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


#27

Yes I have. Still did not work.


#28

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?


#29

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;


#30

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.


#31

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.


#32

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