Insert new record into MSSQL

General
1

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
SQL flow 1796×545 48.8 KB

And here is the debug information:

SQL debug 1
SQL debug 1562×774 37.6 KB

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

2

It should look exactly like you want it.

Example..

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:

3

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

4

Steve,

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

SQL flow 2
SQL flow 21712×869 134 KB

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

5

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.

6

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