# Writing data from SQL query result to a dataset tag

**URL:** <https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607>\
**Category:** Ignition\
**Tags:** ignition81, vision, scripting, database, python\
**Created:** [August 31, 2023, 11:18am UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607 "2023-08-31T11:18:19Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kuldeep\_Pawar](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kuldeep_pawar/32/44736_2.png) [@Kuldeep\_Pawar](https://forum.inductiveautomation.com/u/Kuldeep_Pawar)\
**Post date:** [August 31, 2023, 11:18am UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/1 "2023-08-31T11:18:19Z")

</div>

Hello All,

I am getting dataset ([1R ⅹ 514C]) from a named query. I need to loop through this named query dataset and write this data to a dataset tag in selected columns only (Setpoint ,HiHiLimit , HiLimit , LoLimit , LoLoLimit).  
Below is the tag structure.

 ![Dataset Tag Structure](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/0/109c55245ebc3f301ff9d34daf1a62f03956441f.png)

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [August 31, 2023, 11:20am UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/2 "2023-08-31T11:20:30Z")

</div>

You need all the rows but only a few of the columns?

system.dataset.filterColumns should be able to do that: [system.dataset.filterColumns - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/system.dataset.filterColumns)

---

<div class="post-metadata">

**Author:** ![Kuldeep\_Pawar](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kuldeep_pawar/32/44736_2.png) [@Kuldeep\_Pawar](https://forum.inductiveautomation.com/u/Kuldeep_Pawar)\
**Post date:** [August 31, 2023, 11:46am UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/3 "2023-08-31T11:46:59Z")

</div>

I am reading 514 recipe parameters from SQL table and creating one dictionary. Below is the code for the same.

```auto
dsResult = system.db.runNamedQuery('Recipe/selectRecipe', parameters)
if dsResult.rowCount > 0:
	dictParameter = {}
	for row in range(dsResult.rowCount):
		for col in system.dataset.getColumnHeaders(dsResult):
			key = str(col)
			value = dsResult.getValueAt(row , col)
			if type(value) == float:
				dictParameter[key] = str(round(value , 2))
			else:
				dictParameter[key] = str(value)

```

So based on this dictionary I want to update each row of dataset tag with respective values from the database. Can you give me psuedo code for it.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [August 31, 2023, 11:56am UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/4 "2023-08-31T11:56:21Z")

</div>

So you are reading in a SQL query via a named query, which is a dataset, unpacking that into a dict and then want to repack that dict into a dataset? Why not just take the original dataset from the query and manipulate that into the final dataset. Or better yet just use a query tag to get the data directly from the SQL database into a dataset tag?

---

<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:** [August 31, 2023, 12:04pm UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/5 "2023-08-31T12:04:18Z")

</div>

How is the data coming in from the named query? This is beginning to look like a pivot.

---

<div class="post-metadata">

**Author:** ![Kuldeep\_Pawar](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kuldeep_pawar/32/44736_2.png) [@Kuldeep\_Pawar](https://forum.inductiveautomation.com/u/Kuldeep_Pawar)\
**Post date:** [August 31, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/6 "2023-08-31T12:50:50Z")

</div>

Well I get your point, but on this dataset tag, my UI components also depend. Above code which I pasted in earlier reply is part of large recipe function script. Query tag to dataset tag is not suitable.

---

<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 31, 2023, 1:07pm UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/7 "2023-08-31T13:07:03Z")

</div>

UI components shouldn't be a part of the considerations when designing data handling.  
Nothing prevent your UI from using the tag as its data source.

You showed us the required output, but what does the input looks like ?

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [August 31, 2023, 1:21pm UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/8 "2023-08-31T13:21:01Z")

</div>

OK... so let's backup. And a part of @pascal.fragnoud comment...

Can you describe what the entire system needs to do? And how it is being done currently? That would help us help you.

Sometimes the best way to handle things is to backup and re-evaluate the problem and solution.

---

<div class="post-metadata">

**Author:** ![Kuldeep\_Pawar](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kuldeep_pawar/32/44736_2.png) [@Kuldeep\_Pawar](https://forum.inductiveautomation.com/u/Kuldeep_Pawar)\
**Post date:** [September 7, 2023, 8:55am UTC](https://forum.inductiveautomation.com/t/writing-data-from-sql-query-result-to-a-dataset-tag/78607/9 "2023-09-07T08:55:25Z")

</div>

I have been working around this topic and finally closed this. From the dictionary I have accessed individual parameters and stored them into variable and the using `system.dataset.updateRow` function I have written those values to dataset tag.

Still I wanted to optimize this process of individually reading each parameter from dictionary and updating them into dataset tag (May be a for loop code or something).