0

I am trying to insert Data into a table with where (NOT IN ) clause to avoid duplicate data getting inserted 

nvalue = data.value
nature = data.type
sid = data.Id
cursor.execute("INSERT INTO TABLE_NAME1 (ID, FieldName, FieldValue) VALUES (?, ?, ?) WHERE sid NOT IN (SELECT ID FROM TABLE_NAME1 )", (sid, nature, nvalue)
connection.commit()

ERROR:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHERE'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Also tried replacing the "sid" in where clause with "?" :

cursor.execute("INSERT INTO TABLE_NAME1 (ID, FieldName, FieldValue) VALUES (?, ?, ?) WHERE ? NOT IN (SELECT Id FROM TABLE_NAME1)", (sid, nature, nvalue, sid)
Manish
  • 49
  • 1
  • 10

2 Answers2

0

ID should be used, instead of sid, since it is referring to the column ID for each record in TABLE_NAME1. Meanwhile, sid is the value to be inserted after filtering by WHERE clause. There is no relationship in between.

cursor.execute("INSERT INTO TABLE_NAME1 (ID, FieldName, FieldValue) VALUES (?, ?, ?) WHERE ID NOT IN (SELECT ID FROM TABLE_NAME1 )", (id, nature, nvalue)
Grace
  • 876
  • 1
  • 11
  • 21
  • Thanks for the answer, But i have to check the existing ID present in the table with the sid (Variable) to avoid duplicates – Manish Feb 25 '20 at 12:32
  • It gives the following error - pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHERE'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)") – Manish Feb 26 '20 at 04:14
0

SQL server doesn't support insert into where clause, check this.

As suggested there, you could use 

IF NOT EXISTS(SELECT 1 FROM Payments WHERE ID = ?)
INSERT INTO TABLE_NAME1(ID,FieldName,FieldValue)
VALUES(?,?,?)

using values:

(sid, sid, nature, nvalue)

this means:

cursor.execute("IF NOT EXISTS(SELECT 1 FROM Payments WHERE ID = ?) INSERT INTO TABLE_NAME1(ID,FieldName,FieldValue) VALUES(?,?,?)", (sid, sid, nature, nvalue))

I hope it helps.

Gamopo
  • 1,600
  • 1
  • 14
  • 22
  • Checked with the above suggestion - pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHERE'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)") – Manish Feb 26 '20 at 04:16
  • Okay, I see the problem, I'll change my answer – Gamopo Feb 26 '20 at 08:05