runUpdateQuery - SQL Insert Into Select - multiple datasources

Ignition
1

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.

2

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

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')
3

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.

4

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

5

Thank you Phil, I expected as much.

6

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

7

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

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).