DB Writer query output

Hello everyone,

I have a question regarding KNIME creating SQL inserts. Currently I’m using a DB Writer to generate and write data to a database. However I would like to have the option to get the SQL INSERT statement in a text file so that I can manually execute it in the target system.
As far as I can see there is no option in the DB Writer node for a “read only/do not execute” mode. I was hoping to just get the SQL Insert it is generating without it directly writing it to the database.

Does anyone have an alternative method for this?

Best regards

1 Like

If you have the information to build your insert string, just create a flow for that.

1- Connect to a DB
2- You can use “DB Query Reader” node build your select expression and after manipulate it OR
3- You to get the data using “DB Table Selector” for select the table (you can create a select statment too, but need to use “DB Reader” node to transform it to table rows);
3- Use the “String Manipulation” node to build your query sintaxe, giving the columns values to fill the blank spaces;
4- At least, write the string into a file, like a csv type, passing only the string column.

I hope this help you!

Seeya,

Denis

1 Like

Hi @Sjoerd

See this wf db writer query output.knwf (68.5 KB).

The DB Writer node doesn’t provide the sql. This wf makes it possible to create the sql by using the table properties and flow variables, schema and table (1. directly from the DB Writer node or 2. created with the Variable Creator node) .
Schermafdruk van 2023-03-02 19-27-09
Schermafdruk van 2023-03-02 19-36-41

Hope this helps,
Gr. Hans

2 Likes

Thanks for your suggestions. However writing a string or constructing it as per the examples is not what I would like to do. There is many tables to be constructed and also different database types. While the basic syntax for SQL INSERT statements is similar across different database management systems (DBMS) like Sybase, Oracle, and Postgres, there are some differences in the specific syntax and options available. I do not want to go into all this.
Therefor Im looking for an alternate way and was hoping I can just take what KNIME will generate for me.

1 Like

Well, knime won’t make miracle for all your needs, it’s give you a way to do that. You can use the Executor node to write/build the code seeing the fields and the database structure, but it doesn’t export it AS SQL or other type, just execute… that’s the propous of the knime and others ETL programs, run at the location.

With this work code, you can copy and create your string to insert the values and conditions, and then, export to a csv file, just it…

If you have the data value, it help you until there.

Expression for string manipulation:

join(“INSERT INTO “,$table_name$,” (column1,column2,column3,…) VALUES (”,$value1$,“,”,$value2$,“,”,$value3$,“,…);”)

You have to do it just 1 time at all… or you have so different needs that need to change it always?

Seeya,

Denis

1 Like

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