# What is the canonical way to do a database "upsert" using scripting?

**URL:** <https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785>\
**Category:** General Discussion\
**Tags:** ignition80\
**Created:** [January 30, 2021, 3:41pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785 "2021-01-30T15:41:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\
**Post date:** [January 30, 2021, 3:41pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785/1 "2021-01-30T15:41:36Z")

</div>

Given an Oracle or MS SQL server, what is the canonical way in Jython scripting to do an “upsert” to a table. Where an “upsert” is a a combination of “update” and “insert”. IE if the row doesn’t in a table it is inserted, but if it does exist then it is simply updated.

I see that an “upsert” can be done via a transaction group, but I don’t want to be manually defining new transaction groups whenever I have new data (from different sources) that needs to be inserted/update to a particular table.

My use case is that I have a table in which each row represents the status of a single machine. Right now I have one machine that will place data in that table, but in the future other machines will be slowly added. I want a robust solution that will allow me to define a new machine and have the whole SQL side of things be automatically generated rather than having to manually define a new transaction group each time a new machine comes online.

---

<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 30, 2021, 4:16pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785/2 "2021-01-30T16:16:05Z")

</div>

Upsert is very DB specific, and not all have true atomic implementations. The SQL standard calls for the use of an `ON CONFLICT` clause. I know that PostgreSQL has implemented it (back in 2015) per the standard. Oracle and Microsoft may have, or have a proprietary alternative.

---

<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 30, 2021, 4:21pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785/3 "2021-01-30T16:21:53Z")

</div>

Looks like the closest equivalent for SQL Server is the `MERGE` statement. SQL Server 2019 does not support the standard’s `ON CONFLICT` clause.

---

<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 30, 2021, 4:29pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785/4 "2021-01-30T16:29:06Z")

</div>

Apparently Oracle offers a `MERGE` statement, too.

---

<div class="post-metadata">

**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\
**Post date:** [January 30, 2021, 5:01pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785/5 "2021-01-30T17:01:30Z")

</div>

I should have added that I know there is no direct “upsert” SQL statement for Oracle or MS. What I am looking for is what is the canonical method to construct upsert functionality via Jython scripting. I’m sure I could cobble something together that works, but I am not likely to be following best practices.

I found this interesting blog about [Upsert patterns and anti-patterns](https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/), but that is focussed on stored procedures. That is not a path I want to go down (for DB admin issues at site, not because I can’t write SPs)

---

<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 30, 2021, 6:11pm UTC](https://forum.inductiveautomation.com/t/what-is-the-canonical-way-to-do-a-database-upsert-using-scripting/42785/6 "2021-01-30T18:11:44Z")

</div>

Well, jython in Ignition uses JDBC, so what you might find for JDBC would be appropriate inside of an Ignition DB scripting call. I would use system.db.runPrepUpdate or a named query. In many cases, JDBC will not run a DB script language, just plain statements. If you need to run multiple statements with transaction support, you will need to invoke Ignition’s scripted transaction begin/commit with separate JDBC statements between.
