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.
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.
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) .
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.
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.