# Inserting into multiple tables with single query

**URL:** <https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [March 28, 2022, 2:50pm UTC](https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720 "2022-03-28T14:50:26Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\
**Post date:** [March 28, 2022, 2:50pm UTC](https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720/1 "2022-03-28T14:50:26Z")

</div>

I am trying insert values into two tables using a single query by wrapping the query in a transation.

I created the following namedQuery

```auto
BEGIN;
INSERT INTO notifications_users (user_id,notification_type,username)
 	VALUES ( :user_id , :notification_type , :username );

INSERT INTO notifications_collections (user_id,hash_id)
 	VALUE ( :user_id , :hash_id );
END;

```

Here is the error I get.

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/6/1609700d4a8e8728b9e7d0ee51751ece3e7bae42.png)

Can someone please point out what I am doing wrong.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 28, 2022, 2:55pm UTC](https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720/2 "2022-03-28T14:55:29Z")

</div>

You can’t have multiple statements in a single query due to the JDBC driver. Either use a stored procedure in your database and call it from Ignition, or separate these into separate named queries

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [March 28, 2022, 3:03pm UTC](https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720/3 "2022-03-28T15:03:36Z")

</div>

You can script as a transaction and you can incorporate named queries with it as well though as mentioned, each INSERT will be a separate named query. Check out [system.db.beginNamedQueryTransaction - Ignition User Manual 8.0 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC80/system.db.beginNamedQueryTransaction)

to see an example of how it is done.

---

<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:** [March 28, 2022, 3:11pm UTC](https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720/4 "2022-03-28T15:11:57Z")

</div>

> [@liam1](#):
>
> by wrapping the query in a transation.

What you show is _ **not** _ a transaction. It is simply a SQL script code block. A transaction in a SQL script/procedure is more explicit, like so:

```auto
BEGIN TRANSACTION;

INSERT INTO notifications_users (user_id,notification_type,username)
 	VALUES ( :user_id , :notification_type , :username );

INSERT INTO notifications_collections (user_id,hash_id)
 	VALUE ( :user_id , :hash_id );

COMMIT;

```

But as noted above, JDBC does not support SQL scripts (though some drivers allow it). Use Ignition's transaction management calls instead.

---

<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:** [March 28, 2022, 3:12pm UTC](https://forum.inductiveautomation.com/t/inserting-into-multiple-tables-with-single-query/57720/5 "2022-03-28T15:12:16Z")

</div>

> [@liam1](#):
>
> I am trying insert values into two tables using a single query by wrapping the query in a transation.

Just wan't to point out that you don't need a transaction in order to run an insert into multiple tables. That isn't what a transaction is for. That doesn't mean its a bad idea or that you shouldn't use it here. Just that wrapping it in a transaction wasn't going to make this a single operation.

Also, either you didn't include the entirety of you SQL or your syntax isn't correct because, just using a  
`BEGIN - END` doesn't create a transaction you would need `BEGIN TRANSACTION`

Also, perhaps you haven't made it that far yet, but why use a Transaction if you have no mechanism for deciding to either `COMMIT` or `ROLLBACK`?

Ahhh... @pturmel beat me to it.