Sending CSV Data to MSSQL Server

[red_arrowhead]

I am very new to Node-RED and working on my first flow to send CSV data to MSSQL database tables. I have read the data from CSV files, but unable to solve issues to send the data to MSSQL server. I am getting error that cannot connect to the MSSQL server. How can I send CSV data to MSSQL database?

This is my flow. Please let me know if I am doing it correctly. Thanks

[bakman2]

Please read the documentation
The mssql node uses queries to insert/delete/select data.

[red_arrowhead]

I went through the documentation and tried writing a query to mssql node, but I am still facing issues and errors. I am very new to this platform so can I help me to know how can I proceed

.
Here is the snap shot of my workflow, query( I have written it using very basic knowledge of sql) and errors I am getting.
Please let me know how can I proceed in this case. Thnaks.

[Steve-Mcl]

Well for starters your SQL is complete nonsense.

Start simple.

1. Use a SQL tool (like microsoft sql server management studio) and get a simple SELECT working

2. Copy there working SQL to an MSSQL node and ensure it works

3. Try building the dynamic where clause using {{{mustache}}} syntax (and read the help info and examples provided in the side bar info panel for the MSSQL-PLUS node)

Edit...
The connection is probably not setup correctly either (that's why you're getting connection errors)

Try this simple query

select getdate() as 'servertime'

that query should work on any MS SQL server without having to know what databases or tables it has. Use that as a simple test query to ensure your connection settings are correct.

[red_arrowhead]

Hello, Thanks a lot for the help and information. I tried working with the small functions and now I did able to successfully select the data, tried dynamic values. But I am still not able to understand the mustache format and how to use it.
In MSSQL, I successfully copied the CSV data to my table by writing queries in MSSQL but when I am using the same in node red, I am getting some syntax errors.
The query in my MSSQL is as follows-
BULK INSERT dbo.TEST_City_Data
FROM 'E:\TDK_Sensors_AG_co_KG\US-1\citiesCopy.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
TABLOCK

)

This is working perfect in MSSQL .
But while transferring the same into node red MSSQL node, I am getting errors. How should i proceed?
Thanks a lot

[bakman2]

I am getting errors.

Start with showing us the errors

[red_arrowhead]

Okay. I used the same SQL queries from MSSQL to NODE RED. Can you tell me how to transform them to mustache format. I am confused.
These are the errors which I am getting.
I guess I am messing somewhere with the syntax.

Please have a look and let me know.
Thanks.

[bakman2]

Looking at this line:

FROM 'E:\TDK_Sensors_AG_co_KG\US-1\citiesCopy.csv';

See the ; this is a statement terminator, try removing it.
Question will remain if the node supports local file reading like this.

[red_arrowhead]

I tried it. I think there is some problem with the syntax. Can you help me which how to modify the syntax for Node red. I am unable to find any good documentation for it as well.

[bakman2]

Yes, you use the mustache format for literal values.

I can imagine it to be something like:

WITH FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'

[red_arrowhead]

I tried changing it, but still it has some issue.

So, I do not have to use any mustache format here?
it is used for literal values means for specific values?

[bakman2]

Enclose the WITH parameters with ( )

WITH (
FIRSTROW = ...
...
)

[Steve-Mcl]

As @bakman2 states, your query is incorrect.

FOREWORD: I am using the updated version called MSSQL-PLUS...

• The Original MSSQL node has issues & short commings
  • for example, it doesnt show you the final rendered query (so you cant evaluate what happened to your mustache), cant do multiple queries, gets confused when there is more than one connection, doesnt have extensive built in help. etc, etc, etc
• If you are not using this, then I recommend the following...
  • FIRST uninstall MSSQL
  • THEN install the MSSQL-PLUS version
  • THEN restart node-red

Mustache

As for mustache - its explained in the help info & examples are provided...

Here is a working example...

change node (to set msg.filename, msg.FIRSTROW etc)

the query (with {{{mustache}}})...

the result....