export to excel and name worksheets

Toad for SQL Server
1

My office does not have a good datawarehouse and i can only query against 999 records at a time.

how can i have it loop to run the next set of records and export to excel and name each sheet for the query results, or better yet append to the end of each query?

Most files I’m running are in the 2k-5k records, so multiple queries are needed.

Also, one of my fields is a comment field, so i’m afraid if i export to a cvs i will truncate data.

Thank you
Shanah

2

If you export to the same Excel file you have it append a new unnamed worksheet. There currently is no way to append the data (Although that is a requested enhancement).

To export to a new worksheet you can do this in one of two ways.

  1. Using the export wizard, use the add query type. Add a script that containis multiple SQL. Each select result will be sent to new worksheet. (Note: If you use a named worksheet it will over write it.

IE: Select * from table limit rows 1 -999;
select * from table limit rows 1000 - 19999

The Syntax is not correct but this just shows the idea.

  1. Use automation to execute a loopdataset. Here you can use bind variables in your select.

I’d choose item 1 if you can put together the SQL without binds.

Debbie

3

I use Toad and this is my first week using this tool. My SQL has primarly been via Access.

Can I write the export function in code? I’m not sure how to use the export wizard.

4

You are connecing to SQL Server, correct? Not access. If you are connectig to Access you need to use Toad for Data Analysts.

if you are using SQL Server, you can write a block of code and enter as Query to Export Wizard. See attached screenshots.

Debbie

export2.png
export2.png697×475 43 KB

5

You are connecing to SQL Server, correct? Not access. If you are connectig to Access you need to use Toad for Data Analysts.

if you are using SQL Server, you can write a block of code and enter as Query to Export Wizard. See attached screenshots.

Debbie

export1.png
export1.png699×474 26.4 KB

6

Debbie,
I don’t think i have the export wizard:( I only have Data Pump Export Wizard.

Is there not a command line I can add to the end of my code that will do this?

thanks so much for you help!

7

Shanah, I am a user… So, I have the following questions
image001.gif

8

Shanah, I am a user… So, I have the following questions
image002.jpeg