# How to get system.db.runPrepUpdate into a script variable

**URL:** <https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453>\
**Category:** Ignition\
**Created:** [August 14, 2019, 8:20pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453 "2019-08-14T20:20:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Neil\_B](https://avatars.discourse-cdn.com/v4/letter/n/e95f7d/32.png) [@Neil\_B](https://forum.inductiveautomation.com/u/Neil_B)\
**Post date:** [August 14, 2019, 8:20pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453/1 "2019-08-14T20:20:25Z")

</div>

I’m trying to get the SQL index ID (for use in another INSERT statement).

```auto
system.db.runPrepUpdate("INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?)", [empID, poNum, rmaNum, ledgerNum])

```

When I use the query builder (lets say on a text field) I get the returned index value.

When trying to duplicate this in script, how to do i get the OUTPUT Inserted.ID into a variable?

Edit:  
So ultimately I’m trying to insert a record into a (MS) SQL table, get the unique index (PK) and use that index value for another insert into a daughter (FK) table. Maybe my approach is totally off…

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [August 15, 2019, 6:19pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453/2 "2019-08-15T18:19:16Z")

</div>

```auto
pk=system.db.runPrepUpdate("INSERT INTO Shipping_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?)", [empID, poNum, rmaNum, ledgerNum],getKey=1) 
print pk

```

You need to have the getKey=1 in the call

---

<div class="post-metadata">

**Author:** ![Neil\_B](https://avatars.discourse-cdn.com/v4/letter/n/e95f7d/32.png) [@Neil\_B](https://forum.inductiveautomation.com/u/Neil_B)\
**Post date:** [August 15, 2019, 6:28pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453/3 "2019-08-15T18:28:32Z")

</div>

Ok, getting this error..

> ava.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Shipping\_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?), [55425, 123, 123, 123], , , true, false)
> 
> caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO Shipping\_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?), [55425, 123, 123, 123], , , true, false)  
> caused by GatewayException: SQL error for "INSERT INTO Shipping\_Form ([Requestor Badge Number], [Purchase Order Number], [RMA Number], [Ledger Code Number]) Output Inserted.ID VALUES (?,?,?,?)": A result set was generated for update.  
> caused by SQLServerException: A result set was generated for update.

May I ask where you found that? Seems pretty obscure.

---

<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:** [August 15, 2019, 6:32pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453/4 "2019-08-15T18:32:19Z")

</div>

Get rid of the OUTPUT Inserted.ID.

---

<div class="post-metadata">

**Author:** ![Neil\_B](https://avatars.discourse-cdn.com/v4/letter/n/e95f7d/32.png) [@Neil\_B](https://forum.inductiveautomation.com/u/Neil_B)\
**Post date:** [August 15, 2019, 6:41pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453/5 "2019-08-15T18:41:20Z")

</div>

Perfect, thanks guys!

---

<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:** [August 15, 2019, 7:43pm UTC](https://forum.inductiveautomation.com/t/how-to-get-system-db-runprepupdate-into-a-script-variable/27453/6 "2019-08-15T19:43:11Z")

</div>

> [@Neil\_B](#):
>
> May I ask where you found that? Seems pretty obscure.

It is shown in the users manual.

For Ignition 8 [here](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate)

For Ignition 7.9 [here](https://docs.inductiveautomation.com/display/DOC79/system.db.runPrepUpdate)

The fourth code snipit on the page shows and example of this funtionality.

Just for future reference for a passer by