3305 Main Street Ste 019 Vancouver, WA 98663

How can I combine Sage 100 Contractor data from multiple databases in a single query?

Background

The Liberty Reports Query Wizard will create a query that only uses a single database as the source of the information. This article addresses how to edit the SQL statement of the query so that it will query data from more than one database and combine the data into one set of results.

This approach uses a "UNION" query. Click here to learn more about union queries.

Syntax

The approach to combine data from multiple databases in a single query involves the use of a UNION query. Each part of the UNION query is responsible for querying data from a specific database. The following example starts with a simple query that returns a list of jobs. This query was created using the Liberty Reports query wizard.

SELECT   [recnum] AS [Job],   [jobnme] AS [Job Name] FROM   [actrec]

To amend this query so that it combines the list of jobs from two databases, you would create a UNION query that repeats the same statement and then replace the table name in the FROM clause with the full SQL Instance and Database:

SELECT   [Job1].[recnum] AS [Job],   [Job1].[jobnme] AS [Job Name] FROM   [SQLInstance].[Database1].[actrec] [Job1]

UNION ALL
SELECT   [Job2].[recnum] AS [Job],   [Job2].[jobnme] AS [Job Name] FROM   [SQLInstance].[Database2].[actrec] [Job2]

NOTE - The table name in each part of the UNION is now proceeded by the full SQL Instance and Database. Also, in the above example, "UNION ALL" is used so that the query returns all rows from each part of the query. If the "ALL" keyword is not included, duplicate rows will not be included in the results. It is also important to note that it is good practice to use a Table Alias in "UNION" statements so you may prefix any selected fields in the SQL Statement instead of the full SQL Instance and Database. In the example above, the Table Alias is defined by adding the following after each tale listed in the "FROM" clause:

 [AliasName]

See also