Oct 27, 2017 7:31:41 AM by Mark Kurtz
I recently read a blog post where the writer was talking about importing a spreadsheet into their database. As I was reading this, I asked myself “why”?
Why not just connect to your database and your spreadsheet at the same time and build your query using both of these as data sources?
Obviously, they weren’t using Toad Data Point!
If they were, they would see how easy it is to connect to two--or more--data sources and build a query.
Toad Data Point allows you to connect to two or more data sources at the same time:
For this blog I am going to connect to an Excel spreadsheet and an Oracle 12c database.
Then I am going to choose the “Cross-Connection Query Builder” from the Query Builder dropdown menu:
Now you will see a new window named ‘Query Builder Untitled1*’ (you can rename this by right-clicking on the name tab). Now simply drag-and-drop your spreadsheet into this window:
Then you drag-and-drop your Oracle table into the Query Builder window:
I am using the ‘OW_CUSTOMERS’ table. Now you will see that you have two data sources: your spreadsheet and your database table:
Simply create any join conditions you want to use and click on the fields you want to use in the query:
You can view the query that was generated for you. You can also add WHERE clauses and group by clauses, etc., as you can see above.
This is what the query looks like without any modifications to it:
Now you can execute the query:
And you will see the results in the ‘Results Sets’ grid:
So, no more “importing spreadsheets into my database”. You can now become more productive by connecting to multiple data sources and just building queries as needed.
As an additional benefit, once you have the result set you can right-click on it and start doing more with the data:
Hopefully this blog will help make you a little more efficient when working with spreadsheets and databases.
Written by Mark Kurtz
Mark Kurtz has been with Quest Software since April 2000 and has held the position of Software Sales Engineer in the PreSales Organization since then. Mark started his career as a programmer/analyst for NASA supporting the Space Shuttle program. He became a DBA after moving into the health care industry and has managed several teams of DBA's in his career. Outside of work, Mark enjoys spending time with his family, playing poker, and working on old cars and woodworking.