How do I use 2 databases in one named query

Ignition
1

I need to read data from a table in one database and write to a table in another database in a named update query. Both database connections are setup in the same Ignition gateway. I can't figure out the syntax to reference the 2nd database. I tried using both the actual SQL database name and the Ignition SQL database connection name and neither worked. The 1st database is selected in the "database connection" drop down in the update query.

Here is an example of what I need:

UPDATE database2.dbo.TableToUpdate
SET Field1 = d1.Field1
FROM
(SELECT Field1 FROM database1.dbo.TableToRead) as d1

It gives me invalid object on database2.dbo.TableToUpdate. If I try to put a server name in front of database2 it tells me the server name is not in sys.servers. I am not sure if it wants the Ignition server or the SQL server, so I tried both and neither worked. It tells me I can linked the server, but the server is not a linked server, it is the main server.

2

It's not possible to reference multiple DB connections in a query.

What you're trying to do might be possible if one or both servers are SQL Server and you're using the "linked servers" feature. Otherwise forget it.

3

Wow, thanks for the rapid response. Can I make a named select query to return the dataset and reference this query in another update query?

If not, does this have to be done via python scripting?

4

You'll have to cobble this together in scripting.

5

Thank you. Much cleaner in a named query, but scripting will work, thanks,