Querying Multiple Schemas On Same Database

Toad Data Point & Toad Intelligence Central
1

I’m trying to query multiple schemas within a single database. Each schema has unique data but the same column names. Am I able to do this and combine the similar columns within Query Builder or do I need to just make different queries for each schema?

For example, I want column A and B from the Colorado schema. Then I want the same columns from the Utah schema, column A and B. Then I want to combine columns that are the same, A columns from CO and UT schemas and B columns from CO and UT.

Thanks

2

yes, you can do this. It is a standard use case for this tool. Simply connect to the database, go to the Object Explorer on the left and pick your first schema. Choose the table(s) you want to use from that schema and then go back to the Object Explorer and choose the second schema you want to use. Then choose the table(s) you want to use from that schema.

Once you have your tables in the builder window simply click on the fields you want to use from each table and Toad will generate the query for you.

You may need to create your own joins to speed up the query and you do this by simply dragging one field, say Column A, from one table and dropping it on Column A in the other table.

3

Can’t really do joins between the schems I don’t think as the data does not overlap. One schema only has Colorado data and the other only has Utah data.

They do however have the tables and columns within those tables. How do I aggregate the SITE_NAME column from Colorado with the SITE_NAME column from Utah in the Query Builder? Or should I use the Transform tool for that?

Thanks

4

That would be a UNION query. This technique is like running two separate queries and having the results be in one, single result. One trick to these is that the columns of the second query need to be the same data type as the corresponding columns in the first query. Example: if my first column is text, then the first column in the next query needs to be text, if the second column is datetime, then the second column of the next query needs to be datetime, etc.

5

Example:

SELECT FIRST_NAME, EMP_DOB
FROM SCHEMA_1.EMPLOYEE
UNION
SELECT FIRST_NAME, EMP_DOB
FROM SCHEMA_2.EMPLOYEE

6

I am not sure if this answers your question but I have 2 schemas, dev and prod.

I have the same table structure in both of them.

I have different data in in the tables I am using my customers tables for this example.

When i bring in the tables I see this:

0184.1.jpeg
0184.1.jpeg1205×1051 101 KB

You can see the fields I have checked above.

This is the query that is created:

three.jpeg
three.jpeg1159×587 58.7 KB

This is the data that is returned. I am pulling the 'name' field from both tables and they contain different data sets, yet the results from both tables/fields are displayed.

two.jpeg
two.jpeg1185×1050 113 KB

From here. I can do anything I wish with the data. Export it, profile it, transform it, report on it, etc.

Hopefully this helps but if I am still misunderstanding what you are doing please let me know.

7

Can I do a UNION command through Query Builder or just in the SQL Editor?

8

union.jpeg

9

It can be done via the Builder…there is an U-shaped button on the toolbar. Create your first query, click union, then create the second. A new navigation window will appear where you can switch back and forth when building it. Click back to the first query then run.

10

That worked! Thank you both, much appreciated.