Hello All,
I use Toad for SQL Server at work and we are constantly running the same set of queries and exporting the information so I know how to get the data out.
My question here is can I export all of the different tables into one single excel file all at once.
Example - My query returns Set 1 under my code. Set 1 contains 7 different data tables. 1, 1.1, 1.2 1.3, etc. Is there a way to download 1 through 1.6 all at the same time and have them in different tabs in an excel file? Or am I stuck exporting them one at a time of copying the cells one table tat a time?
Just trying to build in some efficiences for folks in the office. We need all of the tables for our documentaiton, but are having to copy them all one at a time right now.
OK, so it sounds like you are not executing a simple query (e.g. in the Toad Editor), but a script containing multiple queries, and that's why you see multiple data grid "sets" in the same data tab: one data set for each result set from each query Similar to below...
If that's correct, then no, there's currently no direct way from the Data tab to export all of your data sets to one Excel workbook. ALTHOUGH, note that you can select the "All Results" grid and export THAT data set to an Excel file, but you'll get one sheet containing all your data sets' worth of data.
However, there's several ways to accomplish exporting multiple result sets, primarily with the Export Wizard (Tools-> Export -> Export Wizard.) The Export Wizard can perform multiple exports, one query or table at a time. Simply add an additional step in the Wizard for each query in your SQL script. If you plan on repeating these exports for the same script, then save the Export template file at the end of your wizard the first time you define all your exports. Then you can re-open the export template file (TXP) at any time and re-run all your exports. See first snap here.
BONUS is that you can automate these exports to happen and schedule them to run unattended by using the Automation Engine in Toad. Screen snap below shows how to accomplish this:
-
Define your query exports within the Export Wizard and save the template file. Make sure that you choose "Append Worksheet" as part of the export options, as noted.
-
If you choose to automate your exports, Launch Automation (Tools-> Automation), drag the Export Wizard task to the palette, and make sure the task is pointing to the template file you created via the Export Wizard. In the snap below my export template file is called Exp_3queries_1Excel.txp.
-
Whether through the Export Wizard, or calling it via the Automation engine, you should be able to create an Excel worksheet that looks like the example below.
Hope this helps.
