# Issue in using Insert Named Query on a Populated Table

**URL:** <https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117>\
**Category:** Ignition\
**Tags:** database, ignition81, scripting, perspective\
**Created:** [August 24, 2024, 5:50pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117 "2024-08-24T17:50:30Z")\
**Posts on this page:** 1\
**Showing post:** 4

<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:** [August 24, 2024, 7:19pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/4 "2024-08-24T19:19:13Z")

</div>

Frankly, don't bother. Use the approach I suggested, with messages.

Here's my own confirm popup view:

> **view's json**
>
> ```json
> {
> "custom": {},
> "params": {
> "handler": "handler",
> "message": "message",
> "payload": null,
> "popup_id": "popid",
> "send_cancel": true,
> "source_id": null
> },
> "propConfig": {
> "params.handler": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.message": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.payload": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.popup_id": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.send_cancel": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.source_id": {
> "paramDirection": "input",
> "persistent": true
> }
> },
> "props": {
> "defaultSize": {
> "height": 240,
> "width": 380
> }
> },
> "root": {
> "children": [
> {
> "meta": {
> "name": "Label"
> },
> "position": {
> "grow": 1
> },
> "propConfig": {
> "props.text": {
> "binding": {
> "config": {
> "path": "view.params.message"
> },
> "type": "property"
> }
> }
> },
> "props": {
> "style": {
> "overflow": "auto",
> "overflowWrap": "break-word",
> "textAlign": "center"
> }
> },
> "type": "ia.display.label"
> },
> {
> "children": [
> {
> "events": {
> "component": {
> "onActionPerformed": {
> "config": {
> "script": "\t\n\tpayload \u003d {\n\t\t\u0027confirm\u0027: True,\n\t\t\u0027source_id\u0027: self.view.params.source_id\n\t}\n\tif self.view.params.payload:\n\t\tpayload.update(self.view.params.payload)\n\tsystem.perspective.sendMessage(\n\t\tmessageType\t\u003d self.view.params.handler,\n\t\tpayload\t\t\u003d payload,\n\t)\n\tsystem.perspective.closePopup(self.view.params.popup_id)"
> },
> "scope": "G",
> "type": "script"
> }
> }
> },
> "meta": {
> "name": "Button_confirm"
> },
> "position": {
> "basis": "50%"
> },
> "props": {
> "image": {
> "icon": {
> "path": "material/check"
> }
> },
> "text": "Confirmer"
> },
> "type": "ia.input.button"
> },
> {
> "events": {
> "component": {
> "onActionPerformed": {
> "config": {
> "script": "\tif self.view.params.send_cancel:\n\t\tpayload \u003d {\n\t\t\t\u0027confirm\u0027: False,\n\t\t\t\u0027source_id\u0027: self.view.params.source_id\n\t\t}\n\t\tif self.view.params.payload:\n\t\t\tpayload.update(self.view.params.payload)\n\t\tsystem.perspective.sendMessage(\n\t\t\tmessageType\t\u003d self.view.params.handler,\n\t\t\tpayload\t\t\u003d payload,\n\t\t)\n\tsystem.perspective.closePopup(self.view.params.popup_id)"
> },
> "scope": "G",
> "type": "script"
> }
> }
> },
> "meta": {
> "name": "Button_cancel"
> },
> "position": {
> "basis": "50%"
> },
> "props": {
> "image": {
> "icon": {
> "path": "material/close"
> }
> },
> "primary": false,
> "text": "Annuler"
> },
> "type": "ia.input.button"
> }
> ],
> "meta": {
> "name": "buttons"
> },
> "position": {
> "basis": "32px",
> "shrink": 0
> },
> "props": {
> "style": {
> "gap": "10px"
> }
> },
> "type": "ia.container.flex"
> }
> ],
> "meta": {
> "name": "root"
> },
> "props": {
> "direction": "column",
> "style": {
> "gap": "5px",
> "padding": "5px"
> }
> },
> "type": "ia.container.flex"
> }
> }
> 
> ```

Copy the json, create a new flex based view, shift + right click on it and select `paste json`.  
Check the buttons scripts, see how it works, and make your own.

Then, on your `AddBrassTag` popup's root, add a script. In there, you can add a message handler. The name you give it is the value you'll pass to the `handler` parameter of the popup, so that it can send this message when the "confirm" button is clicked.  
In the handler, check `payload['confirm']`, and handle the inserts there.

As a bonus, here's the function I use for bulk inserts:

```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, 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

```

edit: The names of the parameters may be confusing. `handler` is the message handler, `message` is the message that will be displayed in the popup.

---

_[View the full topic](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117)._