Hi
I am looking for a way to edit an SQL statement using variables/tables produced in a Knime workflow. I know SQL statements can be sent using the database reader node, but that does not seem to allow variables to be used instead of the statement. I would also be perfectly happy with another method, eg. using knime nodes. The script needs to run several times, loading multiple files, so it may be worth appending the script for each file.
The statement to be altered is:
(Where [FILE_DATE] is a date/time extracted from the file name, and [FILE] is the file name, both to be replaced by variables. There are other actions performed to table2, but they are static.)
DELETE FROM schema.table1 WHERE file_date=’[FILE_DATE]’;
LOAD DATA LOCAL INFILE’C:/File/Path/[FILE]'
INTO TABLE schema.table2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n’
IGNORE 1 LINES
(col1,col2…);
UPDATE schema.table2
SET file_date=’[FILE_DATE]’;
INSERT IGNORE INTO schema.table1
SELECT *
FROM schema.table2
DELETE FROM schema.table1 WHERE true;
Thanks for your time!