AnsweredAssumed Answered

Inserting multiple entries with SQL parameters?

Question asked by ErikN on Aug 1, 2016
Latest reply on Aug 1, 2016 by khaner

I am using ODBC/OLEDB Provider to insert data into the PI archive. I am using Python/pyodbc to submit these queries with SQL parameters.For a single entry this is working well:.


sql =  "INSERT INTO piarchive..picomp2 (tag, time, value) VALUES (?, ?, CAST(? AS String))"

params = ('TEST_ODBC', '*', 1)

cursor.execute(sql, params)


On Pi Square I found a method to do this using UNION ALL:


INSERT INTO piarchive..picomp2 (tag, time, value)

SELECT 'TEST_ODBC', '*', '3'


UNION ALL SELECT 'TEST_ODBC',  '*-10s'),  '5'


And this works just fine. However if I try to combine the two and try to parameterize the queries I get the following error:


     [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'SELECT'. (0) (SQLPrepare)


I am not sure if this is not possible in SQL or if I am simply making a syntax error...or if there is another way to do this.