# Named Query (insert) with multiple Rows created

**URL:** <https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653>\
**Category:** Ignition\
**Tags:** perspective, scripting\
**Created:** [December 4, 2023, 12:01pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653 "2023-12-04T12:01:01Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Thomas\_Spachinger](https://avatars.discourse-cdn.com/v4/letter/t/d6d6ee/32.png) [@Thomas\_Spachinger](https://forum.inductiveautomation.com/u/Thomas_Spachinger)\
**Post date:** [December 4, 2023, 12:01pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/1 "2023-12-04T12:01:01Z")

</div>

Hello,

i want to insert in a Database a high amout of data, thats reflects multible values from a view (2 values of two different tables, 1 int from an dropdown menu and so on)

my question is now if it is possible to run a named update query where i loop thro the data, collect all values i need and add them to the paramaters for an named update query that looks like:

insert into table (date, value, name) values (Date, int, string)

without having to call the namedQ for each of the 16k updates. Instead i am looking for a way to hand over multible params so that the query looks like:

insert into table (date, value, name) values (Date, int, string), (Date, int, string), (Date, int, string), (Date, int, string) ...

hope i could explain what i am looking for

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2023, 12:42pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/2 "2023-12-04T12:42:20Z")

</div>

A named query cannot do that (with properly secure parameters). A scripted "Prep" update can construct such an insert with many question mark parameter placeholders, but typically not 16k at a time. Most drivers have a limit of ~2000 to ~4000 question marks. But you can definitely send hundreds of rows at a time.

---

<div class="post-metadata">

**Author:** ![Thomas\_Spachinger](https://avatars.discourse-cdn.com/v4/letter/t/d6d6ee/32.png) [@Thomas\_Spachinger](https://forum.inductiveautomation.com/u/Thomas_Spachinger)\
**Post date:** [December 4, 2023, 12:46pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/3 "2023-12-04T12:46:49Z")

</div>

The amount of insert would be dyninamic, so that would not be a way to get the result i want. my main goal with avoiding so many seperate executions of a named query is to keep some performence.  
to reduce the work for the database, is there kind of a singleton connection possible? so that there is onle one connection for the queries made to the DB?  
otherwise i would have to go with a very ugly solution for this problem ☹

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [December 4, 2023, 12:49pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/4 "2023-12-04T12:49:26Z")

</div>

Try this:

```python

def build_insert_string(table, data, columns=None):
	"""
	Build the query string and the values list for a multi-insert query.
	Use with `system.db.runPrepUpdate`
	params:
		table (string): The table to insert into
		data (list of dicts): A list containing a dict for each row to insert, where the keys are the columns names
		columns_names (list of strings):	The columns to be inserted. If None, they're deduced from the first row's keys.
	return:
		a tuple where:
			- the first element is the query string formatted for a `prepUdate` (with question marks as place holders)
			- the second element is the list of values
	"""
	if not data:
		return None
	if columns is None:
		columns = data[0].keys()
	marks = "({})".format(','.join("?" for _ in columns))
	marks = ",".join(marks for _ in data)
	col_names = ','.join(columns)
	q = "insert into {} ({}) values {}".format(table, col_names, marks)
	values = [row[c] for row in data for c in columns]
	return q, values

```

Use it like this:

```python
q, v = build_insert_string('table_name', dict_of_values)
system.db.runPrepUpdate(q, v)

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/5 "2023-12-04T12:50:53Z")

</div>

> [@Thomas\_Spachinger](#):
>
> The amount of insert would be dyninamic

You can dynamically create the SQL for a scripted query. The hundreds of rows would go through a single connection. You can run each of the queries needed to add up to 16k sequentially.

Pascal's example just needs a limiter for the total number of question marks.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 4, 2023, 12:52pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/6 "2023-12-04T12:52:43Z")

</div>

> [@pturmel](#):
>
> A named query cannot do that (with properly secure parameters

Not that I am advising this by any means, but could a QueryString parameter not be made to accomplish this? Or is this considered to be a SQL Script?

Obviously, a Named Query even if it did work would still be limited by the driver, so some type of batching will need to be implemented.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2023, 12:54pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/7 "2023-12-04T12:54:05Z")

</div>

> [@lrose](#):
>
> QueryString parameter not be made to accomplish this

Not securely, no.

Related topic:

> [@runNamedQuery vs runPrepQuery](https://forum.inductiveautomation.com/t/runnamedquery-vs-runprepquery/81268/6):
>
> The original purpose of Named Queries was to make it possible to pass values into Vision Query bindings in a secure and efficient way, solving the unreliability of parameter stringification (looking at you, java.util.Date), and allowing the huge "legacy DB access" security hole to be closed. The maintenance advantages of NQs can be matched by multi-line strings for SQL in library scripts, so I tend to discount that item. However, not all is well. Some things to consider: NQs permit the use…

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 4, 2023, 12:57pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/8 "2023-12-04T12:57:30Z")

</div>

> [@Thomas\_Spachinger](#):
>
> is there kind of a singleton connection possible? so that there is onle one connection for the queries made to the DB?

If you use a transaction then a single connection is consumed until the transaction is closed or it times out.

---

<div class="post-metadata">

**Author:** ![Thomas\_Spachinger](https://avatars.discourse-cdn.com/v4/letter/t/d6d6ee/32.png) [@Thomas\_Spachinger](https://forum.inductiveautomation.com/u/Thomas_Spachinger)\
**Post date:** [December 4, 2023, 1:01pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/9 "2023-12-04T13:01:44Z")

</div>

> [@runNamedQuery vs runPrepQuery](https://forum.inductiveautomation.com/t/runnamedquery-vs-runprepquery/81268/6):
>
> looking at you, java.util.Date

i also have exactly that also in my params i need to hand over to the db -.-

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [December 4, 2023, 1:03pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/10 "2023-12-04T13:03:57Z")

</div>

As long as you use `runPrepQuery` (or a named query with a Datetime parameter though that doesn't really meet your needs in this use case) and the object is a `java.util.Date` or subclass thereof you don't need to do anything to it, Ignition will handle it for you. Simply just `system.db.runPrepUpdate("INSERT INTO table (someDateColumn) VALUES (?)", [someJavaUtilDateVariable])` - this will do it all for you.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2023, 1:04pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/11 "2023-12-04T13:04:34Z")

</div>

> [@Thomas\_Spachinger](#):
>
> i also have exactly that

Use `system.db.runPrepUpdate()` with dynamic SQL and a variable number of parameters. It is the correct tool for this task.

---

<div class="post-metadata">

**Author:** ![Gavin\_Tipker](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@Gavin\_Tipker](https://forum.inductiveautomation.com/u/Gavin_Tipker)\
**Post date:** [December 4, 2023, 1:07pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/12 "2023-12-04T13:07:55Z")

</div>

Maybe you mentioned this and I missed this but what type of database are you trying to write to?

---

<div class="post-metadata">

**Author:** ![Thomas\_Spachinger](https://avatars.discourse-cdn.com/v4/letter/t/d6d6ee/32.png) [@Thomas\_Spachinger](https://forum.inductiveautomation.com/u/Thomas_Spachinger)\
**Post date:** [December 4, 2023, 1:08pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/13 "2023-12-04T13:08:20Z")

</div>

SQL Server

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2023, 1:09pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/14 "2023-12-04T13:09:44Z")

</div>

SQL Server is known to have a limit of just over 2000 question mark parameters per query. Use that to break your complete list of rows into suitably-sized batches.

---

<div class="post-metadata">

**Author:** ![Gavin\_Tipker](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@Gavin\_Tipker](https://forum.inductiveautomation.com/u/Gavin_Tipker)\
**Post date:** [December 4, 2023, 1:18pm UTC](https://forum.inductiveautomation.com/t/named-query-insert-with-multiple-rows-created/82653/15 "2023-12-04T13:18:27Z")

</div>

That makes things easier. I've never been able to insert multiples rows in to Oracle from Ignition. Have to do each one individually.