I need help with an SQL Query between two tables in separate databases.
I want to return all rows from PacakageRecord table on our Bizerba database where both the ArticleNumber from our PackageRecord matches the ItemNumber from OrderStartStop table on our Ignition_Historian_MSSQL database.
I noticed the query builder inside ignition doesn't allow joining between databases, or doesn't look like it does. Is there a way to specify where to look for all the information?
here is what I have currently
SELECT PackageRecord.ActualNetWeightValue, PackageRecord.ArticleNumber
JOIN OrderStartStop ON PackageRecord.ArticleNumber = OrderStartStop.ItemNumber;
If the databases are on the same server, you should be able to join like so (semi-psuedocode below):
FROM Bizerba.<schema>.PacakageRecord pr
INNER JOIN Ignition_Historian_MSSQL.<schema>.OrderStartStop oss
ON oss.ItemNumber = pr.ArticleNumber
If they're not on the same server, you can do this is by creating a linked server and then write your query similar to above. We've done this before with SQL Server and it works exactly as you would expect. I also don't ever use the Database Query Browser tool for anything non-trivial, just use an RDBMS if available and it'll give you suggestions, table names, etc.
You should be able to just write a named query once you get the syntax figured out. For most of the Named Queries I create, there is a corresponding stored procedure in the database that I then just EXEC from the Named Query, but this depends on your requirements and setup (this is more of a description, not a prescription).