# runUpdateQuery - SQL Insert Into Select - multiple datasources

**URL:** <https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [January 31, 2024, 7:57pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046 "2024-01-31T19:57:53Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sam\_Oostendorp](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@Sam\_Oostendorp](https://forum.inductiveautomation.com/u/Sam_Oostendorp)\
**Post date:** [January 31, 2024, 7:57pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/1 "2024-01-31T19:57:53Z")

</div>

I am trying to use a system.db.runUpdateQuery() command to execute an 'Insert Into Select' query to a SQL database.

The SELECT statement is grabbing data from 1 database on 1 server, which has a valid connection in the gateway. The INSERT statement is inserting that data into a 2nd database on a 2nd server, which also has a seperate, valid, connection in the gateway.

I am getting invalid object name errors because the system.db.runUpdateQuery can only accept a single datasource. So depending on which datasource I give it, the error I receive is for the table name used in the query from the opposing datasource.

What would be the best way around this? I know I could separate it into a select query, store all the results in variables in the script, and then write a seperate insert query and pass the data as arguments. However I have to do this for a lot of data across 20+ tables, so I was hoping for something more efficient, less intensive if possible.

---

<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:** [January 31, 2024, 8:15pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/2 "2024-01-31T20:15:59Z")

</div>

Are you just copying whole tables over? Would taking a full db backup or table backups and restoring them be faster?

If you want to do it through Ignition, you could use `DESCRIBE sometable` if using MySQL or whatever the corralary is in your SQL flavor, which would give you a dataset of the column names. Pseudo coding

```auto
tablesToCopy = ['tableA', 'tableB', 'tableC']

for table in tablesToCopy:
    columnData = system.db.runQuery('DESCRIBE %s'%(table), 'dbNumber1')
    selectQueryColumns = [columnName for (columnName, dbType, nullable, key, default, extra) in columnData]
    selectQuery = 'SELECT %s FROM %s'%(', '.join(selectQueryColumns), table)
    selectedData = system.db.runQuery(selectQuery, 'dbNumber1')
    # some tricky list comprehension coming up to make the insert query
    insertQuery = 'INSERT INTO %s (%s) VALUES (%s)'%(table,','.join(ds.columnNames), ', '.join(['?']*len(ds.columnNames)))
    for row in selectedData:
        system.db.runPrepUpdate(insertQuery, list(row), 'dbNumber2')

```

---

<div class="post-metadata">

**Author:** ![Sam\_Oostendorp](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@Sam\_Oostendorp](https://forum.inductiveautomation.com/u/Sam_Oostendorp)\
**Post date:** [January 31, 2024, 8:33pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/3 "2024-01-31T20:33:14Z")

</div>

unfortunately it is not so simple as just copying the entire table. It has to be an insert because other plants are also inserting to the DB. I cannot dump the entirety of the one table into the other, it is small pieces at a time. I will give your solution some more thought though.

---

<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 31, 2024, 8:46pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/4 "2024-01-31T20:46:25Z")

</div>

Separate statements are required. There's no concept of connecting datasources at the JDBC level.

---

<div class="post-metadata">

**Author:** ![Sam\_Oostendorp](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@Sam\_Oostendorp](https://forum.inductiveautomation.com/u/Sam_Oostendorp)\
**Post date:** [January 31, 2024, 8:48pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/5 "2024-01-31T20:48:47Z")

</div>

Thank you Phil, I expected as much.

---

<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 31, 2024, 9:00pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/6 "2024-01-31T21:00:17Z")

</div>

If you have a competent DB platform, you might find this topic useful:

> [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4):
>
> 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: "#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 datase…

---

<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:** [February 1, 2024, 5:23pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-sql-insert-into-select-multiple-datasources/85046/7 "2024-02-01T17:23:13Z")

</div>

What databases are you using exactly for database 1 and 2? Per the first part of my first answer, you can sometimes connect them directly, I've connected SQL Server to MySQL directly like so - [MySQL to SQL Server: Migration guide - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/sql-server/migrate/guides/mysql-to-sql-server?view=sql-server-ver16)

This or something like this (db to db direct) is probably the right way to handle this, Ignition is just really good at being duct tape for a lot of situations so it's looked to first a lot (which I get).