I am trying to execute the following query using 'ExecuteSQL' processor in Apache nifi.
INSERT INTO SampleDB VALUES (${rno}, '${tno}', '${tval}', '${lotno}', '${datval}') WHERE ${rno} NOT IN (SELECT rno FROM SampleDB);
Here the ${rno}
is obtained as flow file attribute.
The following error is obtained on execution:
ExecuteSQL[id=01781107-63a4-1204-8110-6b19db3d5ffc] Unable to execute SQL select query INSERT INTO LimsOnCloud VALUES (1, 'CTG123 ', 'ITM123 ', '123 ', '5 ') WHERE 1 NOT IN (SELECT rno FROM SampleDB); for StandardFlowFileRecord[uuid=93db20b2-5f9f-4521-ac42-11239abb94c2,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1615281573937-132, container=default, section=132], offset=168254, length=152],offset=0,name=098a8ad2-0dc9-4564-a242-8b4855b619b2,size=152] due to Incorrect syntax near the keyword 'WHERE'.; routing to failure: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.
Not able to figure out where the error lies or what is the better way to achieve this.
You should be using proper parameterized queries.
To use a
WHERE
like that, you needSELECT
.You should also specify the column names to insert into explicitly.
NOT IN
falls down in the face of nullables, so favourNOT EXISTS
instead.