# Is it possible to insert multiple records at once with system.db.runPrepUpdate?

**URL:** <https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309>\
**Category:** Ignition\
**Created:** [October 22, 2018, 8:28pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309 "2018-10-22T20:28:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![cbanzet](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbanzet/32/6207_2.png) [@cbanzet](https://forum.inductiveautomation.com/u/cbanzet)\
**Post date:** [October 22, 2018, 8:28pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309/1 "2018-10-22T20:28:02Z")

</div>

I like the way it is possible to insert multiple records into a table using system.db.runUpdateQuery - you just string all of the sets of values together in one string. However, I’ve been bit by SQL injection so I really like using system.db.runPrepUpdate. I haven’t been able to figure out how to insert multiple records at one go though. Here’s the scripting I’ve tried:

```auto
qryflds = "INSERT INTO tblNumbers (field, tenx) VALUES "
values = ""
for x in range(6):
	values += "(%s,%s)," % (x, 10*x)
values = values[:-1]
query = qryflds + values
print query
system.db.runUpdateQuery(query,'BoilerMSSQL')
argslst = []
args = []
for x in range(6,12):
	args = [x,10*x]
	argslst.append(args)
query = qryflds + "(?,?) "
print query, argslst
system.db.runPrepUpdate(query + "(?,?) ", argslst, 'BoilerMSSQL')

```

The first update query works fine - it inserts 6 records into the table. The second query was my best guess at how to do this - system.db.runPrepUpdate requires a list of arguments so I made a list of lists.

Somewhere in the mess of red text I get this,

"Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: SQL error for "INSERT INTO tblNumbers (field, tenx) VALUES (?,?) (?,?) “: The conversion from UNKNOWN to UNKNOWN is unsupported.”

Can this be done or do I need to do it one INSERT at a time?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 22, 2018, 8:35pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309/2 "2018-10-22T20:35:11Z")

</div>

You need commas between each value set.

---

<div class="post-metadata">

**Author:** ![cbanzet](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbanzet/32/6207_2.png) [@cbanzet](https://forum.inductiveautomation.com/u/cbanzet)\
**Post date:** [October 22, 2018, 8:48pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309/3 "2018-10-22T20:48:21Z")

</div>

Hmmmm. So I had a comma between between my value sets in the system.db.runUpdateQuery and it worked.

The  
`
print query, argslst
`  
In my first version showed commas, but of course that was just what Python put in there to interface with the human.

“INSERT INTO tblNumbers (field, tenx) VALUES (?,?) [[6, 60], [7, 70], [8, 80], [9, 90], [10, 100], [11, 110]]”

My attempt to include commas in my second attempt resulted in this code:

```auto
qryflds = "INSERT INTO tblNumbers (field, tenx) VALUES "
values = ""
for x in range(6):
	values += "(%s,%s)," % (x, 10*x)
values = values[:-1]
query = qryflds + values
print query
system.db.runUpdateQuery(query,'BoilerMSSQL')
argslst = []
args = []
for x in range(6,12):
	args = [x,10*x]
	argslst.append(args)
	argslst.append(",")
argslst = argslst[:-1]
query = qryflds + "(?,?) "
print query, argslst
system.db.runPrepUpdate(query + "(?,?) ", argslst, 'BoilerMSSQL')

```

and this output

“INSERT INTO tblNumbers (field, tenx) VALUES (?,?) [[6, 60], ‘,’, [7, 70], ‘,’, [8, 80], ‘,’, [9, 90], ‘,’, [10, 100], ‘,’, [11, 110]]”

from the  
`
print query, argslst
`

And the insert failed again.

I’m not sure how else to get commas between the value sets when system.db.runPrepUpdate needs a Python list of arguments.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 22, 2018, 9:05pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309/4 "2018-10-22T21:05:59Z")

</div>

When doing things like this, I find that the `join()` Python method is extremely helpful. You call it directly on a delimeter string, supplying a sequence of arguments:

```auto
argslst = [[1, 2], [3, 4], [5, 6], [7, 8]]
valueStmt = ', '.join(["(?, ?)"] * len(argslst))

```

returns:  
`(?, ?), (?, ?), (?, ?), (?, ?)`; each element of the sequence provided to `join` is separated by the input string. It’s not the most readable, at first glance, but the output should be exactly what you need:  
`system.db.runPrepUpdate(query % valueStmt, argslst, 'BoilerMSSQL')`

---

<div class="post-metadata">

**Author:** ![cbanzet](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbanzet/32/6207_2.png) [@cbanzet](https://forum.inductiveautomation.com/u/cbanzet)\
**Post date:** [October 22, 2018, 11:34pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-insert-multiple-records-at-once-with-system-db-runprepupdate/20309/5 "2018-10-22T23:34:49Z")

</div>

Thanks Paul.

My understanding of join() is that it just produces a string.

I still haven’t been able to get it to work. I corrected a couple of typos in your suggestion, but still no luck.

When I entered this code:

`
query = "INSERT INTO tblNumbers (field, tenx) VALUES "
argslst = [[1, 2], [3, 4], [5, 6], [7, 8]]
valueStmt = ', '.join(["(?, ?")] * len(argslst))
print query, valueStmt, argslst
system.db.runPrepUpdate(query % valueStmt, argslst, 'BoilerMSSQL')
I got
```auto
  File "<buffer>", line 3
    valueStmt = ', '.join(["(?, ?")] * len(argslst))
                                 ^
SyntaxError: no viable alternative at input ')'

```
I corrected that with this:

query = "INSERT INTO tblNumbers (field, tenx) VALUES "
argslst = [[1, 2], [3, 4], [5, 6], [7, 8]]
valueStmt = ', '.join(["(?, ?)"] * len(argslst))
print query, valueStmt, argslst
system.db.runPrepUpdate(query % valueStmt, argslst, 'BoilerMSSQL')

When I ran that I got:
```auto
Traceback (most recent call last):
  File "<buffer>", line 5, in <module>
TypeError: not all arguments converted during string formatting

```
I corrected that with this:

query = “INSERT INTO tblNumbers (field, tenx) VALUES "
argslst = [[1, 2], [3, 4], [5, 6], [7, 8]]
valueStmt = ', '.join([”(?, ?)"] * len(argslst))
print query, valueStmt, argslst
system.db.runPrepUpdate(query + valueStmt, argslst, ‘BoilerMSSQL’)

The print statement gave me
```auto
INSERT INTO tblNumbers (field, tenx) VALUES (?, ?), (?, ?), (?, ?), (?, ?) [[1, 2], [3, 4], [5, 6], [7, 8]]

```
And I got an explosion of error text, the salient part of which was
```auto
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO tblNumbers (field, tenx) VALUES (?, ?), (?, ?), (?, ?), (?, ?)": The conversion from UNKNOWN to UNKNOWN is unsupported.

```
At this point at least the syntax errors are gone and I’m getting SQL stuff.
And…
I just finally got it to work.
Because all of the ? end up as text in the query, I shouldn’t be supplying a list of lists, it should just be a list.
The crude result that I finally got to work was this.

query = "INSERT INTO tblNumbers (field, tenx) VALUES "
argslst = [1, 2, 3, 4, 5, 6, 7, 8]
valueStmt = ', '.join(["(?, ?)"] * (len(argslst)/2))
print query, valueStmt, argslst
system.db.runPrepUpdate(query + valueStmt, argslst, 'BoilerMSSQL')

The result of that print statement was:
```auto
INSERT INTO tblNumbers (field, tenx) VALUES (?, ?), (?, ?), (?, ?), (?, ?) [1, 2, 3, 4, 5, 6, 7, 8]

```
I’ll automate this a bit and clean it up. Thank you so much for your help.`