# Taking a Dataset and Inserting it to a SQL temp table

**URL:** <https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852>\
**Category:** Ignition\
**Created:** [January 5, 2024, 5:39pm UTC](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852 "2024-01-05T17:39:06Z")\
**Posts on this page:** 1\
**Showing post:** 4

<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:** [January 5, 2024, 7:10pm UTC](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4 "2024-01-05T19:10:59Z")

</div>

If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and `unnest()` them in a FROM clause. For an example, I created a table in my psql forum playground called `phrases`, to be used in a join. When queried by itself, you get this dataset:

```auto
"#NAMES"
"prefix","suffix"
"#TYPES"
"str","I"
"#ROWS","4"
"Hello","1"
"Good Bye","2"
"Welcome to","3"
"Whoa!","4"

```

To go with that, in a custom property named `lookups`, I created the following unbound dataset:

```auto
"#NAMES"
"id","name"
"#TYPES"
"I","str"
"#ROWS","4"
"1","World!"
"2","Cruel World."
"3","Happy Valley!"
"4","Uncanny Valley?"

```

In a simple button's `actionPerformed` event, I placed this code:

```auto
import jarray
from java.lang import String, Integer
root = event.source.parent
sql = """
SELECT concat(phrases.prefix, ' ', suffixes.suffix) As phrase
FROM phrases INNER JOIN
  unnest(?::int[], ?::varchar[]) as suffixes(key, suffix)
  ON phrases.suffix = suffixes.key
"""
lists = [root.lookups.getColumnAsList(i) for i in range(root.lookups.columnCount)]
params = [jarray.array(lists[0], Integer), jarray.array(lists[1], String)]
ds = system.db.runPrepQuery(sql, params)
root.joined = ds.underlyingDataset

```

When executed, the `joined` custom property received this result dataset:

```auto
"#NAMES"
"phrase"
"#TYPES"
"str"
"#ROWS","4"
"Hello World!"
"Good Bye Cruel World."
"Welcome to Happy Valley!"
"Whoa! Uncanny Valley?"

```

I recommend you migrate to a database technology that has `unnest()` functionality in some form. (I can't find any way to do this with MS SQL Server.)

---

_[View the full topic](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852)._